﻿using Coldairarrow.Entity.Base;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.IT;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.TD;
using Coldairarrow.Entity.View;
using Coldairarrow.IBusiness;
using Coldairarrow.IBusiness.DTO;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_OutsourcingBillBusiness : BaseBusiness<PB_OutsourcingBill>, IPB_OutsourcingBillBusiness, ITransientDependency
    {
        readonly IOperator _operator;
        readonly ICheckOperation _check;
        readonly IOperatorDept _opdept;
        public PB_OutsourcingBillBusiness(IDbAccessor db, IOperator @operator, ICheckOperation @check, IOperatorDept @opdept)
            : base(db)
        {
            _operator = @operator;
            _check = @check;
            _opdept = @opdept;
        }

        #region 外部接口

        public async Task<PageResult<PB_OutsourcingBillViewDTO>> GetDataListAsync(PageInput<PB_OutsourcingBilSelectDTO>  input)
        {
            Expression<Func<PB_OutsourcingBill, PB_ProductionOrder, PB_Project, PB_Supplier, PB_OutsourcingBillViewDTO>> select = (a, b, c, d) => new PB_OutsourcingBillViewDTO
            {
                ProdName = b.POName,
                ProjectName =c.Name,
                SupplierName =d.Name
            };
            var search = input.Search;
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_ProductionOrder>() on a.RefCode equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Project>() on a.ProjectId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Supplier>() on a.OutsourcingCompanyId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d);
            if (!search.Status.IsNullOrEmpty())
            {
                q = q.Where(x => x.Status == search.Status);
            }
            if (!search.InputName.IsNullOrEmpty())
            {
                q = q.Where(x => x.Id.Contains(search.InputName) || x.ProjectName.Contains(search.InputName) );
            }
            //q = q.Where(x => x.Status <= 4);
            return await q.GetPageResultAsync(input);

        }

        public async Task<PB_ProductionDataListDTO> GetTheDataAsync(string id)
        {
            PB_ProductionDataListDTO pblist = new PB_ProductionDataListDTO();
            PB_OutsourcingBill Datalist = await Db.GetIQueryable<PB_OutsourcingBill>().Where(x => x.Id == id).FirstOrDefaultAsync();
            
            //将父类对象赋值转换成子类对象
            pblist = AutoAdaptation.AutoCopy<PB_OutsourcingBill, PB_ProductionDataListDTO>(Datalist);
            PB_ProductionOrder orderdata = await Db.GetIQueryable<PB_ProductionOrder>().Where(x => x.Id == Datalist.RefCode).FirstOrDefaultAsync();
            pblist.OrderName = orderdata == null ? "" : orderdata.POName;

            Expression<Func<PB_OutsourcingBillDetail, PB_Material, PB_MaterialType, Base_UnitGroup, PB_OutsourcingBillDetailDTO>> select = (a, b, c, d) => new PB_OutsourcingBillDetailDTO
            {

                //MaterialID = b.Id,//物料编号
                MaterialName = b.Name,//物料名称
                MaterialTypeId = c.Name,//类型名称
                MaterialCode = b.Code,//物料编号
                MaterialSpec = b.Spec, //物料规格
                Weight = a.Weight,//理论重量
                UnitGroupName = d.Name,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_OutsourcingBillDetail>().AsExpandable().Where(x => x.ParentId == id)
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_MaterialType>() on b.MaterialTypeId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_UnitGroup>() on b.UnitGroupId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d);
            //q = q.Where();
            pblist.OutsourcingBillDetail = await q.ToListAsync();
            //pblist.OutsourcingBillDetail = await Db.GetIQueryable<PB_OutsourcingBillDetail>().Where(x=>x.ParentId == id).ToListAsync();
            return pblist;
            //return await GetEntityAsync(id);
        }
        /// <summary>
        /// 打印外协加工单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<PB_ProductionDataListDTO> GetTheDataInfoPrintAsync(string id)
        {
            PB_ProductionDataListDTO pblist = new PB_ProductionDataListDTO();
            Expression<Func<PB_OutsourcingBill, PB_ProductionOrder, PB_Project, PB_Supplier, PB_ProductionDataListDTO>> select = (a, b, c, d) => new PB_ProductionDataListDTO
            {
                ProdName = b.POName,
                ProjectName = c.Name,
                SupplierName = d.Name
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable().Where(x => x.Id == id)
                    join b in Db.GetIQueryable<PB_ProductionOrder>() on a.RefCode equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Project>() on a.ProjectId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Supplier>() on a.OutsourcingCompanyId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d);
            PB_ProductionDataListDTO Datalist = await q.FirstOrDefaultAsync();
            //将父类对象赋值转换成子类对象
            pblist = AutoAdaptation.AutoCopy<PB_ProductionDataListDTO, PB_ProductionDataListDTO>(Datalist);
            Expression<Func<PB_OutsourcingBillDetail, PB_Material, PB_MaterialType, Base_UnitGroup, PB_OutsourcingBillDetailDTO>> selects = (a, b, c, d) => new PB_OutsourcingBillDetailDTO
            {
                MaterialName = b.Name,//物料名称
                MaterialTypeId = c.Name,//类型名称
                MaterialCode = b.Code,//物料编号
                MaterialSpec = b.Spec, //物料规格
                Weight = a.Weight,//理论重量
                UnitGroupName = d.Name,
            };
            selects = selects.BuildExtendSelectExpre();
            var w = from a in Db.GetIQueryable<PB_OutsourcingBillDetail>().AsExpandable().Where(x => x.ParentId == id)
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_MaterialType>() on b.MaterialTypeId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_UnitGroup>() on b.UnitGroupId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @selects.Invoke(a, b, c, d);
            pblist.OutsourcingBillDetail = await w.ToListAsync();
            return pblist;
        }




        /// <summary>
        /// 外包单保存
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [DataAddLog(UserLogType.外包订单, "Id", "外包订单")]
        [Transactional]
        public async Task AddDataAsync(PB_OutsourcingBillDTO data)
        {
            Base_User udata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            PB_OutsourcingBill outbilldata = new PB_OutsourcingBill();
            outbilldata.Id = data.Id;
            outbilldata.CreatorId = _operator.UserId;
            outbilldata.CreateTime = data.CreateTime;
            outbilldata.Deleted = false;
            outbilldata.BillCode = GetTimeStamp();
            outbilldata.OutsourcingDate = data.OutsourcingDate;
            outbilldata.RefCode = data.RefCode;
            outbilldata.OutsourcingCompanyId = data.OutsourcingCompanyId;
            outbilldata.ExpectedDate = data.ExpectedDate;
            outbilldata.ProjectId = data.ProjectId;
            outbilldata.WorkingProcedureId = data.WorkingProcedureId;
            outbilldata.Rate = data.Rate;
            outbilldata.TheoreticalWT = data.TheoreticalWT;
            outbilldata.Price = data.Price;
            outbilldata.Amount = data.Amount;
            outbilldata.Weight = data.Weight;
            outbilldata.Remark = data.Remark;
            outbilldata.Status = 0;
            outbilldata.DeptId = udata.DepartmentId;
            outbilldata.QrCode = o_outsour + "," + data.Id;
            outbilldata.OutName = data.OutName;
            outbilldata.BackWeight = 0;
            outbilldata.OutBillType = 0;

            List<PB_OutsourcingBillDetail> listdata = new List<PB_OutsourcingBillDetail>();
            foreach (var item in data.OutsourcingBillDetail)
            {
                string itemId = IdHelper.GetId();
                PB_OutsourcingBillDetail itemdata = new PB_OutsourcingBillDetail();
                itemdata.Id = itemId;
                itemdata.CreatorId = data.CreatorId;
                itemdata.CreateTime = data.CreateTime;
                itemdata.Deleted = false;
                itemdata.MaterialId = item.MaterialId;
                itemdata.Qty = item.Qty;
                itemdata.TheoreticalWT = item.TheoreticalWT;
                itemdata.Weight = item.Weight;
                itemdata.Remark = item.Remark;
                itemdata.ParentId = data.Id;
                itemdata.QrCode = o_OutsourcingBillDetail + "," + itemId;
                itemdata.ReadyNums = 0;
                itemdata.RejectNums = 0;
                itemdata.State = 0;
                listdata.Add(itemdata);
            }
            await Db.InsertAsync<PB_OutsourcingBillDetail>(listdata);

            await Db.InsertAsync<PB_OutsourcingBill>(outbilldata);

        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [DataEditLog(UserLogType.外包订单, "Id", "外包订单")]
        [Transactional]
        public async Task UpdateDataAsync(PB_OutsourcingBillDTO data)
        {
            await ExecuteSqlAsync("delete from PB_OutsourcingBillDetail  where ParentId ='" + data.Id + "'");


            List<PB_OutsourcingBillDetail> listdata = new List<PB_OutsourcingBillDetail>();
            foreach (var item in data.OutsourcingBillDetail)
            {
                string itemId = IdHelper.GetId();
                PB_OutsourcingBillDetail itemdata = new PB_OutsourcingBillDetail();
                itemdata.Id = itemId;
                itemdata.CreatorId = data.CreatorId;
                itemdata.CreateTime = data.CreateTime;
                itemdata.Deleted = false;
                itemdata.MaterialId = item.MaterialId;
                itemdata.Qty = item.Qty;
                itemdata.TheoreticalWT = item.TheoreticalWT;
                itemdata.Weight = item.Weight;
                itemdata.Remark = item.Remark;
                itemdata.ParentId = data.Id;
                itemdata.QrCode = o_OutsourcingBillDetail + "," + itemId;
                itemdata.ReadyNums = 0;
                itemdata.RejectNums = 0;
                itemdata.State = 0;
                listdata.Add(itemdata);
            }
            await Db.InsertAsync<PB_OutsourcingBillDetail>(listdata);

            Base_User udata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            PB_OutsourcingBill pB_OutsourcingBill = await Db.GetIQueryable<PB_OutsourcingBill>().Where(s => s.Id == data.Id).FirstOrDefaultAsync();
            pB_OutsourcingBill.OutName = data.OutName;
            pB_OutsourcingBill.BillCode = data.BillCode;
            pB_OutsourcingBill.OutsourcingDate = data.OutsourcingDate;
            pB_OutsourcingBill.RefCode = data.RefCode;
            pB_OutsourcingBill.OutsourcingCompanyId = data.OutsourcingCompanyId;
            pB_OutsourcingBill.ExpectedDate = data.ExpectedDate;
            pB_OutsourcingBill.ProjectId = data.ProjectId;
            pB_OutsourcingBill.WorkingProcedureId = data.WorkingProcedureId;
            pB_OutsourcingBill.Rate = data.Rate;
            pB_OutsourcingBill.TheoreticalWT = data.TheoreticalWT;
            pB_OutsourcingBill.Price = data.Price;
            pB_OutsourcingBill.Amount = data.Amount;
            pB_OutsourcingBill.Weight = data.Weight;
            pB_OutsourcingBill.Remark = data.Remark;
            pB_OutsourcingBill.QrCode = o_outsour + "," + data.Id; //二维码
            pB_OutsourcingBill.OutBillType = 0;
            pB_OutsourcingBill.Status = 0;
            pB_OutsourcingBill.DeptId = udata.DepartmentId;
            List<string> props = GetFieldList(pB_OutsourcingBill);
            if (props.IsNullOrEmpty())
            {
                await UpdateAsync(pB_OutsourcingBill);
            }
            else
            {
                await UpdateAsync(pB_OutsourcingBill, props);
            }
        }

        [DataDeleteLog(UserLogType.外包订单, "Id", "外包订单")]
        public async Task DeleteDataAsync(List<string> ids)
        {
            string detailIds = string.Join("','", ids.ToArray());
            string sqlMain = @"select * from PB_OutsourcingBillUpList where ParentId in ('" + detailIds + "')";
            List<PB_OutsourcingBillUpList> listdata = await Db.GetListBySqlAsync<PB_OutsourcingBillUpList>(sqlMain);
            if (listdata.Count > 0)
            {
                throw new BusException("有外包单已有出库不能删除！");
            }
            sqlMain = @"DELETE from PB_OutsourcingBillDetail where ParentId in ('" + detailIds + "')";
            await ExecuteSqlAsync(sqlMain);
            await DeleteAsync(ids);
        }
        /// <summary>
        /// 审核成功
        /// </summary>
        /// <param name="audit"></param>
        /// <returns></returns>
        public async Task Approve(AuditDTO audit)
        {
            var data = await this.GetEntityAsync(audit.Id);
            data.Status = 1;
            data.AuditTime = audit.AuditTime;
            data.AuditUserId = audit.AuditUserId;
            await UpdateAsync(data);
        }
        /// <summary>
        /// 审核失败
        /// </summary>
        /// <param name="audit"></param>
        /// <returns></returns>
        public async Task Reject(AuditDTO audit)
        {
            var data = await GetEntityAsync(audit.Id);
            data.Status = 2;
            data.AuditTime = audit.AuditTime;
            data.AuditUserId = audit.AuditUserId;
            await UpdateAsync(data);
        }
        /// <summary>
        /// 签收成功 (作废)
        /// </summary>
        /// <param name="audit"></param>
        /// <returns></returns>
        public async Task Signin(AuditDTO audit)
        {
            var data = await GetEntityAsync(audit.Id);
            data.Status = 5;
            data.SignforTime = audit.AuditTime;
            data.SignforNameId = audit.AuditUserId;
            await UpdateAsync(data);
        }

        /// <summary>
        /// 外协开始作业 (作废)
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public async Task Start(string Id)
        {  
            string sqlMain = $" update PB_OutsourcingBill set Status= 6 where Id='{Id}'; ";
            await ExecuteSqlAsync(sqlMain);
        }
        /// <summary>
        /// 外包签收
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [Transactional]
        public async Task RejectDataAsync(PB_OutsourcingBillDTO data)
        {
            double? nums = 0;
            double? rnums = 0;
            int index = 0;
            foreach (var item in data.OutsourcingBillDetail)
            {
                if (item.Qty == item.RejectNums)
                {
                    if (index != 1)
                    {
                        index = 2;
                    }
                }
                else
                {
                    index = 1;
                }
                nums += item.Qty;
                if (item.RejectNums > 0)
                {
                    rnums += item.RejectNums;
                    await Db.UpdateAsync<PB_OutsourcingBillDetail>(w => w.Id == item.Id, entity => { entity.RejectNums = item.RejectNums; });
                }
                
            }
            if (nums == rnums && index == 2)
            {
                string sqlMain = @"update PB_OutsourcingBill  set SignforStatus = 2,Status= 6, SignforNameId = '"+data.SignforNameId+"' where Id='"+data.Id+"'; ";
                await ExecuteSqlAsync(sqlMain);
            }
            else
            {
                string sqlMain = @"update PB_OutsourcingBill set SignforStatus = 1,Status= 6, SignforNameId = '"+data.SignforNameId+"' where Id='"+data.Id+"'; ";
                await ExecuteSqlAsync(sqlMain);
            }
        }


        #endregion

        #region PDA 出库
        /// <summary>
        /// 1.扫描外协单,返回外协单号
        /// </summary>
        /// <param name="outsourqrcode"></param>
        /// <returns></returns>
        public async Task<PB_OutsourcingBillDetail> GetPDADataListAsync(string outsourqrcode)
        {
            string outsourid = outsourqrcode.Split(',')?[1].ToString();
            return await Db.GetIQueryable<PB_OutsourcingBillDetail>().AsExpandable().Where(x => x.Id == outsourid).FirstOrDefaultAsync();
        }


        public async Task<List<PB_UnLoadListDataDTO>> GetOutsourcingUpDetailListDataAsync(string outsourqrcode)
        {
            string outsourid = outsourqrcode.Split(',')?[1].ToString();
            string sqlMain = @" select * from ( select b.PackId,b.MaterialId,c.BoxNo,c.BoxName,(concat(e.`Name`,'/',f.`Name`,'/',d.`Name`)) as LocalName,g.Spec as MaterialSpec,b.Nums as StockNumber,b.CreateTime,c.QrCode from PB_OutsourcingBillDetail as a join PB_OutsourcingBillUpList as b on a.Id=b.ParentId join PB_PackList as c on b.PackId=c.Id join PB_Location as d on b.LocalId=d.Id join PB_StorArea as e on d.AreaId=e.Id join PB_Rack as f on d.RackId=f.Id left join PB_Material as g on b.MaterialId=g.Id where a.Id='" + outsourid + "' and  c.PackageType=0     UNION ALL 	 select PackId,'' as MaterialId,BoxNo,BoxName,LocalName,'' as MaterialSpec,SUM(StockNumber) as StockNumber,CreateTime,QrCode from (	select b.PackId,b.MaterialId,c.BoxNo,c.BoxName,(concat(e.`Name`,'/',f.`Name`,'/',d.`Name`)) as LocalName,g.Spec as MaterialSpec,b.Nums as StockNumber,DATE_FORMAT(b.CreateTime,'%Y-%m-%d') as CreateTime,c.QrCode from PB_OutsourcingBillDetail as a join PB_OutsourcingBillUpList as b on a.Id=b.ParentId join PB_PackList as c on b.PackId=c.Id join PB_Location as d on b.LocalId=d.Id join PB_StorArea as e on d.AreaId=e.Id join PB_Rack as f on d.RackId=f.Id left join PB_Material as g on b.MaterialId=g.Id where a.Id='" + outsourid + "' and  c.PackageType=1 )as t group by PackId,BoxNo,BoxName,LocalName,CreateTime,QrCode     UNION all 	select b.PackId,b.MaterialId,c.Id as BoxNo,c.Name as BoxName,(concat(e.`Name`,'/',f.`Name`,'/',d.`Name`)) as LocalName,c.Spec as MaterialSpec,b.Nums as StockNumber,b.CreateTime,concat('04,',b.MaterialId) as QrCode from PB_OutsourcingBillDetail as a join PB_OutsourcingBillUpList as b on a.Id=b.ParentId join PB_Material as c on b.MaterialId=c.Id join PB_Location as d on b.LocalId=d.Id join PB_StorArea as e on d.AreaId=e.Id join PB_Rack as f on d.RackId=f.Id where a.Id='" + outsourid + "' and b.PackId='' ) as t order by CreateTime desc ";
            return await Db.GetListBySqlAsync<PB_UnLoadListDataDTO>(sqlMain);
        }


        /// <summary>
        /// 判断下架的物料对应的项目是否和外协单上的项目一致
        /// </summary>
        /// <param name="packqrcode">包编码</param>
        /// <param name="outsourqrcode">外协单</param>
        /// <returns></returns>
        public async Task<bool> GetLocationProjectTruOrFalseAsync(string packqrcode, string outsourqrcode)
        {
            bool bl = true;
            //包Id
            //包编号/物料编号
            string packId = string.Empty;
            string materialId = string.Empty;
            string outsourid = string.Empty;
            string packmodel = packqrcode.Split(',')?[0].ToString();
            var models = outsourqrcode.Split(',');
            if (models.Length > 2)
            {
                outsourid = outsourqrcode.Split(',')[2].ToString();
            }
            else
            {
                outsourid = outsourqrcode.Split(',')[1].ToString();
            }


            if (packmodel == "13")
            {
                packId = packqrcode.Split(',')?[1].ToString();
            }
            else if (packmodel == "04")
            {
                var model = packqrcode.Split(',');
                if (model.Length > 2)
                {
                    materialId = model?[3].ToString();
                }
                else
                {
                    materialId = model?[1].ToString();
                }
            }

            //获取这个外协单
            Expression<Func<PB_OutsourcingBill, PB_OutsourcingBillDetail, PB_OutsourcingBillViewDTO>> selects = (a, b) => new PB_OutsourcingBillViewDTO
            {
                OutsourcingBillDetailId = b.Id
            };

            selects = selects.BuildExtendSelectExpre();
            var w = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_OutsourcingBillDetail>() on a.Id equals b.ParentId into ab
                    from b in ab.DefaultIfEmpty()

                    select @selects.Invoke(a, b);
            var pickdata = await w.Where(x => x.OutsourcingBillDetailId == outsourid).FirstOrDefaultAsync();


            if (packmodel == "13")
            {
                //查询包在仓库对应的货位的信息
                Expression<Func<PB_Location, IT_LocalDetail, PB_LocationProjectDTO>> select = (a, b) => new PB_LocationProjectDTO
                {
                    PackId = b.PackId
                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<PB_Location>().AsExpandable()
                        join b in Db.GetIQueryable<IT_LocalDetail>() on a.Id equals b.LocalId into ab
                        from b in ab.DefaultIfEmpty()
                        select @select.Invoke(a, b);
                var locadata = await q.Where(x => x.PackId == packId).FirstOrDefaultAsync();

                if (locadata != null)
                {
                    //货位是否有项目信息
                    if (locadata.ProjectId.IsNullOrEmpty())
                    {
                        bl = true;
                    }
                    else
                    {
                        if (locadata.ProjectId == pickdata.ProjectId)
                        {
                            bl = true;
                        }
                        else
                        {
                            bl = false;
                        }
                    }
                }
            }
            else
            {
                PB_OutsourcingBillUpList pbdata = await Db.GetIQueryable<PB_OutsourcingBillUpList>().Where(x => x.ParentId == outsourid).FirstOrDefaultAsync();
                if (pbdata != null)
                {
                    PB_Location locadata = await Db.GetIQueryable<PB_Location>().Where(x => x.Id == pbdata.LocalId).FirstOrDefaultAsync();
                    if (locadata != null)
                    {
                        //货位是否有项目信息
                        if (locadata.ProjectId.IsNullOrEmpty())
                        {
                            bl = true;
                        }
                        else
                        {
                            if (locadata.ProjectId == pickdata.ProjectId)
                            {
                                bl = true;
                            }
                            else
                            {
                                bl = false;
                            }
                        }
                    }
                }
            }
            return bl;
        }

        public async Task<PB_DispatchDetailPDANumberDTO> GetMaterialDataAsync(string packqrcode, string localqrcode)
        {
            var packmodel = packqrcode.Split(',')?[0];
            string packid = string.Empty;
            if (packmodel == "04")
            {
                var model = packqrcode.Split(',');
                if (model.Length > 2)
                {
                    if (model.Length < 4)
                    {
                        throw new BusException("请扫描或输入正确的物料二维码");
                    }
                    else
                    {
                        packid = model?[3].ToString();
                    }
                }
                else
                {
                    packid = model?[1].ToString();
                }
            }
            //查询外协字表找出物料名称
            //Expression<Func<PB_Material, PB_OutsourcingBillDetail, PB_OutsourUpListDataDTO>> select = (a, b) => new PB_OutsourUpListDataDTO
            //{
            //    Nums = b.Qty,
            //    PickId = b.ParentId
            //};
            //select = select.BuildExtendSelectExpre();
            //var q = from a in Db.GetIQueryable<PB_Material>().AsExpandable()
            //        join b in Db.GetIQueryable<PB_OutsourcingBillDetail>() on a.Id equals b.MaterialId into ab
            //        from b in ab.DefaultIfEmpty()
            //        select @select.Invoke(a, b);
            //return await q.Where(x => x.Id == packid && x.PickId == outsourqrcode.Split(',')[1].ToString()).FirstOrDefaultAsync();
            PB_DispatchDetailPDANumberDTO list = new PB_DispatchDetailPDANumberDTO();
            Expression<Func<PB_Material, IT_LocalMaterial, PB_Location, PB_Rack, PB_StorArea, PB_DispatchDetailPDANumberDTO>> select = (a, b, c, d, e) => new PB_DispatchDetailPDANumberDTO
            {
                LocalId = b.LocalId,
                PlaceName = e.Name + "/" + d.Name + "/" + c.Name
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_Material>().AsExpandable()
                    join b in Db.GetIQueryable<IT_LocalMaterial>() on a.Id equals b.MaterialId into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Location>() on b.LocalId equals c.Id into bc
                    from c in bc.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Rack>() on b.RackId equals d.Id into bd
                    from d in bd.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_StorArea>() on b.AreaId equals e.Id into be
                    from e in be.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e);

            list = await q.Where(x => x.Id == packid && x.LocalId == localqrcode.Split(',')[1].ToString()).FirstOrDefaultAsync();
            if (list == null)
                throw new BusException("请扫描或输入正确的物料二维码");
            else
                return list;
        }


        /// <summary>
        /// 4 输入数量,点击确定,执行
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [Transactional]
        public async Task UpdatePDAOutsourcingUpData(OutsourcingData data)
        {
            //包编号(Id)
            string packid = string.Empty;
            //物料编号(ID)
            string materialId = string.Empty;
            //外协单号(ID)
            string outsourId = string.Empty;
            var models = data.outsourqrcode.Split(',');
            if (models.Length > 2)
            {
                outsourId = data.outsourqrcode.Split(',')[2].ToString();
            }
            else
            {
                outsourId = data.outsourqrcode.Split(',')[1].ToString();
            }
            //货位编号(ID)
            string localId = data.localqrcode.Split(',')?[1].ToString();

            string packmodel = data.packqrcode.Split(',')?[0].ToString();
            
            if (packmodel == "13")
            {
                packid = data.packqrcode.Split(',')?[1].ToString();
            }
            else if (packmodel == "04")
            {
                var model = data.packqrcode.Split(',');
                if (model.Length > 2)
                {
                    materialId = model?[3].ToString();
                }
                else
                {
                    materialId = model?[1].ToString();
                }
            }
            Base_User bdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            //--------------------------------------------------------- 上面变量 ----------------------------------------------------



            //如果是物料包  PackageType = 0 :"标准":"混合"
            if (packmodel == "13")
            {
                PB_PackList PB_PackListdata = await Db.GetIQueryable<PB_PackList>().Where(x => x.Id == packid).FirstOrDefaultAsync();
                if (PB_PackListdata == null)
                {
                    throw new BusException("请扫描正确的包二维码!");
                }
                //如果是标准包
                if (PB_PackListdata.PackageType == 0)
                {
                    //获取仓库要出库的包信息
                    Expression<Func<IT_LocalDetail, PB_PackList, StockPackListDTO>> packselect = (a, b) => new StockPackListDTO
                    {
                        BoxName = b.BoxName,
                        PackageType = b.PackageType
                    };
                    packselect = packselect.BuildExtendSelectExpre();
                    var packdata = from a in Db.GetIQueryable<IT_LocalDetail>().AsExpandable()
                                   join b in Db.GetIQueryable<PB_PackList>() on a.PackId equals b.Id into ab
                                   from b in ab.DefaultIfEmpty()
                                   select @packselect.Invoke(a, b);
                    var pack = await packdata.Where(x => x.PackId == packid && x.LocalId == localId).FirstOrDefaultAsync();
                    if (pack != null)
                    {
                        var trueorfalse = await _check.GetTrueOrFalse(pack.MaterialId, pack.StorId, localId);
                        if (trueorfalse.boolType == false)
                        {
                            throw new BusException("仓库物料正在盘点中禁止下架出库");
                        }

                        IT_LocalMaterial materdata = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == pack.MaterialId && x.StorId == pack.StorId && x.LocalId == pack.LocalId).FirstOrDefaultAsync();
                        if (materdata == null)
                        {
                            throw new BusException("请检查库存物料是否存在");
                        }
                        else
                        {
                            double? dnum = materdata.Nums_O + materdata.Nums_N;
                            if (pack.Num > dnum)
                            {
                                throw new BusException("请检查库存物料数量是否满足!");
                            }
                        }

                        //获取外协备货单明细信息
                        var detaildata = await Db.GetIQueryable<PB_OutsourcingBillDetail>().Where(x => x.Id == outsourId ).FirstOrDefaultAsync();
                        if (detaildata != null)
                        {
                            //增加外协备货单明细日志
                            PB_OutsourcingBillUpList pbdata = new PB_OutsourcingBillUpList();
                            pbdata.Id = IdHelper.GetId();
                            pbdata.ParentId = detaildata.Id;
                            pbdata.LocalId = pack.LocalId;
                            pbdata.MaterialId = detaildata.MaterialId;
                            pbdata.Nums = pack.Num;
                            pbdata.CreatorId = _operator.UserId;
                            pbdata.CreatorRealName = bdata.RealName;
                            pbdata.CreateTime = DateTime.Now;
                            pbdata.Deleted = false;
                            await Db.InsertAsync<PB_OutsourcingBillUpList>(pbdata);
                            //增加出库日志
                            PB_StockLog pblog = new PB_StockLog();
                            pblog.Id = IdHelper.GetId();
                            pblog.PackId = pack.PackId;
                            pblog.StorId = pack.StorId;
                            pblog.LocationId = pack.LocalId;
                            pblog.StockNumber = pack.Num;
                            pblog.CreatorId = _operator.UserId;
                            pblog.CreatorName = bdata.RealName;
                            pblog.OperatorId = _operator.UserId;
                            pblog.Type = 1;
                            pblog.OrderID = outsourId;
                            pblog.CreateTime = DateTime.Now;
                            pblog.State = 1;
                            await Db.InsertAsync<PB_StockLog>(pblog);

                            //获取货位信息
                            var locationdata = await Db.GetIQueryable<PB_Location>().Where(x => x.Id == pack.LocalId).FirstOrDefaultAsync();

                            //删除库存
                            await ExecuteSqlAsync("delete from PB_Stock where PackId='" + pack.PackId + "'");

                            //删除库存明细表
                            await ExecuteSqlAsync("delete from IT_LocalDetail where PackId='" + pack.PackId + "'");

                            //修改货架上存放货物存汇总表的审核前数量和新旧数量
                            string itsql = string.Empty;
                            if (pack.OldOrNew == 0)
                            {
                                itsql = @"update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + pack.Num + ",ScanNum=IFNULL(ScanNum,0)-" + pack.Num + " where StorId='" + locationdata.StorId + "' and LocalId='" + locationdata.Id + "' and MaterialId='" + pack.MaterialId + "'";
                            }
                            else if (pack.OldOrNew == 1)
                            {
                                itsql = @"update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + pack.Num + ",ScanNum=IFNULL(ScanNum,0)-" + pack.Num + " where StorId='" + locationdata.StorId + "' and LocalId='" + locationdata.Id + "' and MaterialId='" + pack.MaterialId + "'";
                            }
                            await ExecuteSqlAsync(itsql);

                            //修改物料审核前数量
                            string msql = @"update PB_Material set ScanNum=IFNULL(ScanNum,0)-" + pack.Num + " where Id='" + pack.MaterialId + "'";
                            await ExecuteSqlAsync(msql);

                            //修改外协备货单物料的已备货数量
                            await ExecuteSqlAsync("update PB_OutsourcingBillDetail set ReadyNums=IFNULL(ReadyNums,0)+" + pack.Num + " where Id='" + detaildata.Id + "'");

                            //修改包的状态为已出库
                            await ExecuteSqlAsync("update PB_PackList set Status=4,OutStorTime=NOW() where Id='" + pack.PackId + "'");
                        }
                        else
                        {
                            throw new BusException("请扫描正确的包信息");
                        }
                    }
                    else
                    {
                        throw new BusException("请扫描正确的货位上的包信息");
                    }
                }
                else if (PB_PackListdata.PackageType == 1)
                {
                    //获取仓库要出库的包信息
                    Expression<Func<IT_LocalDetail, PB_PackList, PB_OutsourcingBillDetail, StockPackListDTO>> packselect = (a, b, c) => new StockPackListDTO
                    {
                        BoxName = b.BoxName,
                        PackageType = b.PackageType,
                        PickUpMainId = c.ParentId,
                        PickUpListId = c.Id
                    };
                    packselect = packselect.BuildExtendSelectExpre();
                    var packdata = from a in Db.GetIQueryable<IT_LocalDetail>().AsExpandable()
                                   join b in Db.GetIQueryable<PB_PackList>() on a.PackId equals b.Id into ab
                                   from b in ab.DefaultIfEmpty()
                                   join c in Db.GetIQueryable<PB_OutsourcingBillDetail>() on a.MaterialId equals c.MaterialId into ac
                                   from c in ac.DefaultIfEmpty()
                                   select @packselect.Invoke(a, b, c);
                    var pack = await packdata.Where(x => x.PackId == packid && x.PickUpMainId == outsourId && x.LocalId == localId).ToListAsync();
                    if (pack.Count > 0)
                    {
                        string strLocalId = string.Empty;
                        string strStorId = string.Empty;
                        foreach (var item in pack)
                        {
                            strLocalId = item.LocalId;
                            strStorId = item.StorId;
                            var trueorfalse = await _check.GetTrueOrFalse(item.MaterialId, item.StorId, localId);
                            if (trueorfalse.boolType == false)
                            {
                                throw new BusException("仓库物料正在盘点中禁止下架出库");
                            }
                            IT_LocalMaterial materdata = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == item.MaterialId && x.StorId == item.StorId && x.LocalId == item.LocalId).FirstOrDefaultAsync();
                            if (materdata == null)
                            {
                                throw new BusException("请检查库存物料是否存在");
                            }
                            else
                            {
                                double? dnum = materdata.Nums_O + materdata.Nums_N;
                                if (item.Num > dnum)
                                {
                                    throw new BusException("请检查库存物料数量是否满足!");
                                }
                            }
                            //获取货位信息
                            var locationdata = await Db.GetIQueryable<PB_Location>().Where(x => x.Id == item.LocalId).FirstOrDefaultAsync();

                            //修改货架上存放货物存汇总表的审核前数量和新旧数量
                            string itsql = string.Empty;
                            if (item.OldOrNew == 0)
                            {
                                itsql = @"update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'";
                            }
                            else if (item.OldOrNew == 1)
                            {
                                itsql = @"update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'";
                            }
                            await ExecuteSqlAsync(itsql);

                            //修改物料审核前数量
                            string msql = @"update PB_Material set ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where Id='" + item.MaterialId + "'";
                            await ExecuteSqlAsync(msql);

                            //修改外协单字表 单物料的已备货数量
                            await ExecuteSqlAsync("update PB_OutsourcingBillDetail set ReadyNums=IFNULL(ReadyNums,0)+" + item.Num + " where Id='" + outsourId + "' and MaterialId='" + item.MaterialId + "'; ");
                            //增加备货单明细日志
                            PB_OutsourcingBillUpList pbdata = new PB_OutsourcingBillUpList();
                            pbdata.Id = IdHelper.GetId();
                            pbdata.ParentId = item.PickUpListId;
                            pbdata.LocalId = item.LocalId;
                            pbdata.MaterialId = item.MaterialId;
                            pbdata.Nums = item.Num;
                            pbdata.CreatorId = _operator.UserId;
                            pbdata.CreatorRealName = bdata.RealName;
                            pbdata.CreateTime = DateTime.Now;
                            pbdata.Deleted = false;
                            await Db.InsertAsync<PB_OutsourcingBillUpList>(pbdata);
                        }
                        //增加出库日志
                        PB_StockLog pblog = new PB_StockLog();
                        pblog.Id = IdHelper.GetId();
                        pblog.PackId = packid;
                        pblog.MaterialId = "";
                        pblog.StorId = strStorId;
                        pblog.LocationId = strLocalId;
                        pblog.StockNumber = PB_PackListdata.Nums;
                        pblog.CreatorId = _operator.UserId;
                        pblog.CreatorName = bdata.RealName;
                        pblog.OperatorId = _operator.UserId;
                        pblog.Type = 1;
                        pblog.OrderID = outsourId;
                        pblog.CreateTime = DateTime.Now;
                        pblog.State = 1;
                        await Db.InsertAsync<PB_StockLog>(pblog);

                        //删除库存
                        await ExecuteSqlAsync("delete from PB_Stock where PackId='" + packid + "'");

                        //删除库存明细表
                        await ExecuteSqlAsync("delete from IT_LocalDetail where PackId='" + packid + "'");

                        //修改包的状态为已出库
                        await ExecuteSqlAsync("update PB_PackList set Status=4,OutStorTime=NOW() where Id='" + packid + "'");
                    }
                    else
                    {
                        throw new BusException("请扫描正确的货位上的包信息");
                    }
                }
            }
            else if (packmodel == "04")
            {
                //查询库存,是否有这个物料的库存
                List<IT_LocalDetail> IT_LocalDetaillist = await Db.GetIQueryable<IT_LocalDetail>().Where(x => x.MaterialId == materialId && x.PackId == "" && x.LocalId == localId).ToListAsync();
                //如果这个物料的库存大于0
                if (IT_LocalDetaillist.Count()>0)
                {
                    //汇总当前货位的物料总数量
                    var matersumnums = IT_LocalDetaillist.GroupBy(x => new { x.MaterialId, x.StorId, x.LocalId }).Select(x => new { MaterialId = x.Key.MaterialId, LocalId = x.Key.LocalId, StorId = x.Key.StorId, materNums = x.Sum(i => i.Num) }).FirstOrDefault();
                    //如果取的数量大于库存数量,
                    if (data.Nums > matersumnums.materNums)
                    {
                        throw new BusException("请检查当前货位物料的数量是否满足出库的数量");
                    }
                    //获取要出库的单个物料数量
                    double? warehouseNum = data.Nums;
                    string itsql = string.Empty;
                    string strLocalId = string.Empty;
                    string strStorId = string.Empty;
                    foreach (var item in IT_LocalDetaillist)
                    {
                        if (warehouseNum > 0)
                        {
                            strLocalId = item.LocalId;
                            strStorId = item.StorId;
                            //出库数量等于库存明细数量时  修改库存表 主表
                            if (item.Num == warehouseNum)
                            {
                                warehouseNum -= item.Num;
                                if (item.OldOrNew == 0)
                                {
                                    itsql += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                else if (item.OldOrNew == 1)
                                {
                                    itsql += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                itsql += @" delete from IT_LocalDetail where Id='" + item.Id + "'; ";

                            }
                            else if (item.Num < warehouseNum)   //出库数量大于库存明细数量时
                            {
                                if (warehouseNum >= item.Num)
                                {
                                    warehouseNum -= item.Num;
                                    if (item.OldOrNew == 0)
                                    {
                                        itsql += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    else if (item.OldOrNew == 1)
                                    {
                                        itsql += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    itsql += @" delete from IT_LocalDetail where Id='" + item.Id + "'; ";
                                }
                                else
                                {
                                    warehouseNum -= warehouseNum;
                                    if (item.OldOrNew == 0)
                                    {
                                        itsql += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    else if (item.OldOrNew == 1)
                                    {
                                        itsql += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    itsql += @" update IT_LocalDetail set Num=IFNULL(Num,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                                }
                            }
                            else if (item.Num > warehouseNum) //出库数量小于库存明细数量时
                            {
                                item.Num -= warehouseNum;
                                if (item.OldOrNew == 0)
                                {
                                    itsql += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                else if (item.OldOrNew == 1)
                                {
                                    itsql += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                itsql += @" update IT_LocalDetail set Num=IFNULL(Num,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                                warehouseNum -= warehouseNum;
                            }

                        }
                        else
                        {
                            continue;
                        }
                    }
                    //修改外协字表领取数量  PB_OutsourcingBillDetail 
                    itsql += @" update PB_OutsourcingBillDetail set ReadyNums=IFNULL(ReadyNums,0)+" + data.Nums + " where Id='" + outsourId + "'; ";
                    await ExecuteSqlAsync(itsql);

                    //获取要出库的单个物料数量
                    warehouseNum = data.Nums;
                    itsql = "";
                    strLocalId = "";
                    strStorId = "";

                    List<PB_Stock> PB_Stocklist = await Db.GetIQueryable<PB_Stock>().Where(x => x.MaterialId == materialId && x.PackId == "").ToListAsync();
                    if (PB_Stocklist.Count > 0)
                    {
                        foreach (var item in PB_Stocklist)
                        {
                            if (warehouseNum != 0)
                            {
                                strLocalId = item.LocationId;
                                strStorId = item.StorId;
                                //出库数量等于库存明细数量时
                                if (item.StockNumber == warehouseNum)
                                {
                                    warehouseNum -= item.StockNumber;
                                    itsql += @" delete from PB_Stock where Id='" + item.Id + "'; ";
                                }
                                else if (item.StockNumber < warehouseNum)   //出库数量大于库存明细数量时
                                {
                                    if (warehouseNum >= item.StockNumber)
                                    {
                                        warehouseNum -= item.StockNumber;
                                        itsql += @" delete from PB_Stock where Id='" + item.Id + "'; ";
                                    }
                                    else
                                    {
                                        warehouseNum -= warehouseNum;
                                        itsql += @" update PB_Stock set StockNumber=IFNULL(StockNumber,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                                    }
                                }
                                else if (item.StockNumber > warehouseNum) //出库数量小于库存明细数量时
                                {
                                    item.StockNumber -= warehouseNum;
                                    itsql += @" update PB_Stock set StockNumber=IFNULL(StockNumber,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                                }
                            }
                            else
                            {
                                continue;
                            }
                        }
                        await ExecuteSqlAsync(itsql);
                    }
                    PB_OutsourcingBillDetail pickupdata = await Db.GetIQueryable<PB_OutsourcingBillDetail>().Where(x => x.Id == outsourId).FirstOrDefaultAsync();
                    //增加备货单明细日志
                    PB_OutsourcingBillUpList pbdata = new PB_OutsourcingBillUpList();
                    pbdata.Id = IdHelper.GetId();
                    pbdata.ParentId = pickupdata.Id;
                    pbdata.LocalId = strLocalId;
                    pbdata.MaterialId = materialId;
                    pbdata.Nums = data.Nums;
                    pbdata.CreatorId = _operator.UserId;
                    pbdata.CreatorRealName = bdata.RealName;
                    pbdata.CreateTime = DateTime.Now;
                    pbdata.Deleted = false;
                    await Db.InsertAsync<PB_OutsourcingBillUpList>(pbdata);
                    //增加出库日志
                    PB_StockLog pblog = new PB_StockLog();
                    pblog.Id = IdHelper.GetId();
                    pblog.PackId = packid;
                    pblog.StorId = strStorId;
                    pblog.LocationId = strLocalId;
                    pblog.StockNumber = data.Nums;
                    pblog.CreatorId = _operator.UserId;
                    pblog.CreatorName = bdata.RealName;
                    pblog.OperatorId = _operator.UserId;
                    pblog.Type = 1;
                    pblog.OrderID = outsourId;
                    pblog.CreateTime = DateTime.Now;
                    pblog.State = 1;
                    await Db.InsertAsync<PB_StockLog>(pblog);
                }
                else
                {
                    throw new BusException("请检查货位上的库存物料是否满足需求");
                }
            }
        }

        /// <summary>
        /// 下架出库提交    
        /// </summary>
        /// <param name="outsourqrcode"></param>
        /// <returns></returns>
        [Transactional]
        public async Task SubmitPDAOutsourcingUpDataAsync(string outsourqrcode)
        {
            string outsourId = string.Empty;//外协单Id
            //外协单ID
            //string outsourId = outsourqrcode.Split(',')?[1].ToString();
            var models = outsourqrcode.Split(',');
            if (models.Length > 2)
            {
                outsourId = outsourqrcode.Split(',')[2].ToString();
            }
            else
            {
                outsourId = outsourqrcode.Split(',')[1].ToString();
            }
            //用户信息
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            Expression<Func<PB_OutsourcingBillDetail, PB_Material, PB_OutsourcingBillUpList,PB_OutsourcingPriceDTO>> packselect = (a, b,c) => new PB_OutsourcingPriceDTO
            {
                Price = b.Price,
                LocalId =c.LocalId,
                ReadyupNums = c.Nums
            };
            packselect = packselect.BuildExtendSelectExpre();
            var packdata = from a in Db.GetIQueryable<PB_OutsourcingBillDetail>().AsExpandable()
                           join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                           from b in ab.DefaultIfEmpty()
                           join c in Db.GetIQueryable< PB_OutsourcingBillUpList>() on a.Id equals c.ParentId into ac
                           from c in ac.DefaultIfEmpty()
                           select @packselect.Invoke(a, b,c);
            var OutsourcUpData = packdata.Where(x => x.Id == outsourId).ToList();
            List<string> listdata = new List<string>();
            string sqlMain = "";       
            sqlMain += $@" update PB_OutsourcingBillDetail set State=3 where Id='" + outsourId + "'; ";
            //修改备货单明细状态
            await ExecuteSqlAsync(sqlMain);

            //定义备货单状态
            int status = 0;
            //if (indexstate == 1)
            //{
            status = 3;
            double dbnum = 0;
            string outid = IdHelper.GetId();
            //出库明细
            List<TD_OutStorDetail> listdetaildata = new List<TD_OutStorDetail>();
            int i = 0;
            //仓库ID
            string StorId = string.Empty;
            //货区
            string AreaId = string.Empty;
            //货架
            string RackId = string.Empty;

            foreach (var item in OutsourcUpData)
            {
                if (i == 0) 
                {
                    var locadata = await Db.GetIQueryable<PB_Location>().Where(x => x.Id == item.LocalId).FirstOrDefaultAsync();
                    StorId = locadata.StorId;
                    AreaId = locadata.AreaId;
                    RackId = locadata.RackId;
                }
                TD_OutStorDetail outdetaildata = new TD_OutStorDetail();
                outdetaildata.Id = IdHelper.GetId();
                outdetaildata.StorId = StorId;
                outdetaildata.OutStorId = outid;
                outdetaildata.LocalId = item.LocalId;
                outdetaildata.AreaId = AreaId;
                outdetaildata.RackId = RackId;
                outdetaildata.MaterialId = item.MaterialId;
                outdetaildata.OutNum = Convert.ToDouble(item.ReadyupNums);
                outdetaildata.Price = Convert.ToDouble(item.Price);
                outdetaildata.TotalAmt = Convert.ToDouble(item.ReadyupNums) * Convert.ToDouble(item.Price);
                dbnum += Convert.ToDouble(item.ReadyupNums);
                outdetaildata.CreatorId = _operator.UserId;
                outdetaildata.CreateTime = DateTime.Now;
                outdetaildata.Deleted = false;
                listdetaildata.Add(outdetaildata);
                i++;
            }
            if (dbnum == 0)
            {
                throw new BusException("出库数量为0不能提交");
            }
            await Db.InsertAsync<TD_OutStorDetail>(listdetaildata);


            //添加出库明细表
            List<TD_OutStorRecord> listtrecorddata = new List<TD_OutStorRecord>();
            var listd = listdetaildata.GroupBy(x => new { x.StorId, x.LocalId, x.OutStorId, x.MaterialId, x.Price }).Select(x => new { StorId = x.Key.StorId, LocalId = x.Key.LocalId, OutStorId = x.Key.OutStorId, MaterialId = x.Key.MaterialId, Price = x.Key.Price, Nums = x.Sum(n => n.OutNum) });
            foreach (var item in listd)
            {
                TD_OutStorRecord recodata = new TD_OutStorRecord();
                recodata.Id = IdHelper.GetId();
                recodata.StorId = item.StorId;
                recodata.OutStorId = item.OutStorId;
                recodata.MaterialId = item.MaterialId;
                recodata.OutNum = item.Nums;
                recodata.Price = item.Price;
                recodata.TotalAmt = item.Nums * item.Price;
                recodata.CreateTime = DateTime.Now;
                recodata.CreatorId = _operator.UserId;
                recodata.Deleted = false;
                listtrecorddata.Add(recodata);
            }
            await Db.InsertAsync<TD_OutStorRecord>(listtrecorddata);

            //var Mainlist = await Db.GetIQueryable<PB_OutsourcingBill>().AsExpandable().Where(x=>x.Id == outsourId).FirstOrDefaultAsync();
            Expression<Func<PB_OutsourcingBill, PB_OutsourcingBillDetail, PB_OutsourcingBillViewDTO>> selects = (a, b) => new PB_OutsourcingBillViewDTO
            {
                OutsourcingBillDetailId = b.Id
            };

            selects = selects.BuildExtendSelectExpre();
            var w = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_OutsourcingBillDetail>() on a.Id equals b.ParentId into ab
                    from b in ab.DefaultIfEmpty()

                    select @selects.Invoke(a, b);
            var Mainlist = await w.Where(x => x.OutsourcingBillDetailId == outsourId).FirstOrDefaultAsync();

            //创建出库单
            TD_OutStorage outdata = new TD_OutStorage();
            outdata.Id = outid;
            outdata.Code = DateTime.Now.ToString("yyyyMMddHHmmss");
            outdata.OutTime = DateTime.Now;
            outdata.StorageId = StorId;
            outdata.OutType ="OutSourcing";
            outdata.RefCode = outsourId;
            outdata.ProjectId = Mainlist.ProjectId; //项目ID
            outdata.CusId = Mainlist.OutsourcingCompanyId; //客户ID
            outdata.OutNum = dbnum;
            outdata.CreatorId = _operator.UserId;
            outdata.CreateTime = DateTime.Now;
            outdata.Status = 0;
            outdata.Deleted = false;
            outdata.QrCode = h_OutStorage + "," + outid;
            outdata.DepId = userdata.FactoryId;
            outdata.FactoryId = userdata.FactoryId;
            await Db.InsertAsync<TD_OutStorage>(outdata);

            //更新占用库存量
            var listdetdata = OutsourcUpData.GroupBy(x => new { x.MaterialId, x.ReadyupNums, x.LocalId }).Select(x => new { MaterialId = x.Key.MaterialId, StockVolume = x.Key.ReadyupNums, LocationId = x.Key.LocalId }).ToList();
            string sqlMains = string.Empty;
            foreach (var item in listdetdata)
            {
                sqlMains += @" update IT_LocalMaterial set Nums_R=IFNULL(Nums_R,0)-" + item.StockVolume + " where MaterialId='" + item.MaterialId + "' and LocalId='" + item.LocationId + "'; ";
            }
            //清楚库存备用单占用量
            await ExecuteSqlAsync(sqlMains);


        }

        /// <summary>
        /// 移除外协备货单已出库的明细  
        /// </summary>
        /// <param name="outsourqrcode"></param>
        /// <param name="packqrocde"></param>
        /// <param name="nums"></param>
        /// <returns></returns>
        [Transactional]
        public async Task DeletePDAOutsourcingUpDataAsync(string outsourqrcode, string packqrocde, double nums)
        {

            Base_User basedata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            //string outsourId = outsourqrcode.Split(',')?[1].ToString();   //外协单Id
            //外协单号(ID)
            string outsourId = string.Empty;//外协单Id
            var models = outsourqrcode.Split(',');
            if (models.Length > 2)
            {
                outsourId = outsourqrcode.Split(',')[2].ToString();
            }
            else
            {
                outsourId = outsourqrcode.Split(',')[1].ToString();
            }
            string packid = string.Empty;
            string materialId = string.Empty;
            string packmodel = packqrocde.Split(',')?[0].ToString();
            if (packmodel == "13")
            {
                packid = packqrocde.Split(',')?[1].ToString();
            }
            else if (packmodel == "04")
            {
                var model = packqrocde.Split(',');
                if (model.Length > 2)
                {
                    materialId = model?[3].ToString();
                }
                else
                {
                    materialId = model?[1].ToString();
                }
            }
            //---------------------------------------------定义变量 --------------------------------------------------------------------

            //如果是包
            if (packmodel == "13")
            {
                string sqlMain = string.Empty;
                PB_PackList pbdata = await Db.GetIQueryable<PB_PackList>().Where(x => x.Id == packid).FirstOrDefaultAsync();
                //如果是标准包
                if (pbdata.PackageType ==0)
                {
                    Expression<Func<PB_OutsourcingBillUpList, PB_Location, PB_OutsourcingBillDetail, PB_Material, PB_OutsourcingBillUpListDTO>> packselect = (a, b, c, d) => new PB_OutsourcingBillUpListDTO
                    {
                        LocationId = b.Id,
                        StorId = b.StorId,
                        AreaId = b.AreaId,
                        RackId = b.RackId,
                        OutsourcingBillId = c.ParentId,
                        OutsourcingBillDetailId = c.Id,
                        MeasureId = d.storageId,
                    };
                    packselect = packselect.BuildExtendSelectExpre();
                    var packdata = from a in Db.GetIQueryable<PB_OutsourcingBillUpList>().AsExpandable()
                                   join b in Db.GetIQueryable<PB_Location>() on a.LocalId equals b.Id into ab
                                   from b in ab.DefaultIfEmpty()
                                   join c in Db.GetIQueryable<PB_OutsourcingBillDetail>() on new { a.ParentId, a.MaterialId } equals new { c.ParentId, c.MaterialId } into ac
                                   from c in ac.DefaultIfEmpty()
                                   join d in Db.GetIQueryable<PB_Material>() on a.MaterialId equals d.Id into ad
                                   from d in ad.DefaultIfEmpty()
                                   select @packselect.Invoke(a, b, c, d);
                    var unloaddata = await packdata.FirstOrDefaultAsync();
                    //删除备货出库的明细
                    sqlMain += @" delete from PB_OutsourcingBillUpList where PackId='" + packid + "'; ";
                    //修改包的状态为入库
                    sqlMain += @" update PB_PackList set Status=3 where Id='" + packid + "'; ";
                    //减去备货单明细的出库数量
                    sqlMain += @" update PB_OutsourcingBillDetail set ReadyNums=IFNULL(ReadyNums,0)-" + pbdata.Nums + " where ParentId='" + outsourId + "' and MaterialId='" + pbdata.MaterialId + "'; ";
                    await ExecuteSqlAsync(sqlMain);
                    PB_Stock stockdata = new PB_Stock();
                    stockdata.Id = IdHelper.GetId();
                    stockdata.PackId = packid;
                    stockdata.StorId = unloaddata.StorId;
                    stockdata.LocationId = unloaddata.LocationId;
                    stockdata.StockNumber = pbdata.Nums;
                    stockdata.MaterialId = pbdata.MaterialId;
                    stockdata.CreatorId = _operator.UserId;
                    stockdata.CreatorName = basedata.RealName;
                    stockdata.CreateTime = DateTime.Now;
                    stockdata.OperatorId = _operator.UserId;
                    stockdata.Type = 1;
                    stockdata.OrderID = outsourId;
                    stockdata.OldOrNew = pbdata.OldOrNew;
                    stockdata.AreaId = unloaddata.AreaId;
                    stockdata.RackId = unloaddata.RackId;
                    await Db.InsertAsync<PB_Stock>(stockdata);

                    PB_StockLog stockdatalog = new PB_StockLog();
                    stockdatalog.Id = IdHelper.GetId();
                    stockdatalog.PackId = packid;
                    stockdatalog.StorId = unloaddata.StorId;
                    stockdatalog.LocationId = unloaddata.LocationId;
                    stockdatalog.StockNumber = pbdata.Nums;
                    stockdatalog.MaterialId = pbdata.MaterialId;
                    stockdatalog.CreatorId = _operator.UserId;
                    stockdatalog.CreatorName = basedata.RealName;
                    stockdatalog.CreateTime = DateTime.Now;
                    stockdatalog.OperatorId = _operator.UserId;
                    stockdatalog.Type = 1;
                    stockdatalog.OrderID = outsourId;
                    stockdatalog.OldOrNew = pbdata.OldOrNew;
                    stockdatalog.State = 0;
                    await Db.InsertAsync<PB_StockLog>(stockdatalog);

                    IT_LocalDetail itdata = new IT_LocalDetail();
                    itdata.Id = IdHelper.GetId();
                    itdata.StorId = unloaddata.StorId;
                    itdata.LocalId = unloaddata.LocationId;
                    itdata.MaterialId = pbdata.MaterialId;
                    itdata.MeasureId = unloaddata.MeasureId;
                    itdata.BarCode = pbdata.QrCode;
                    itdata.InTime = DateTime.Now;
                    itdata.CreateTime = DateTime.Now;
                    itdata.CreatorId = _operator.UserId;
                    itdata.Deleted = false;
                    itdata.Num = pbdata.Nums;
                    itdata.PackId = packid;
                    itdata.OldOrNew = pbdata.OldOrNew;
                    itdata.JobId = "";
                    await Db.InsertAsync<IT_LocalDetail>(itdata);

                    sqlMain = "";
                    if (pbdata.OldOrNew == 0)
                    {
                        sqlMain = @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)+" + pbdata.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + pbdata.Nums + " where MaterialId='" + pbdata.MaterialId + "' and StorId='" + unloaddata.StorId + "' and LocalId='" + unloaddata.LocationId + "' ";
                    }
                    else if (pbdata.OldOrNew == 1)
                    {
                        sqlMain = @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)+" + pbdata.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + pbdata.Nums + " where MaterialId='" + pbdata.MaterialId + "' and StorId='" + unloaddata.StorId + "' and LocalId='" + unloaddata.LocationId + "' ";
                    }
                    await ExecuteSqlAsync(sqlMain);


                }
                //如果是混合包
                else if (pbdata.PackageType == 1)
                {
                    Expression<Func<PB_OutsourcingBillUpList, PB_Location, PB_OutsourcingBillDetail, PB_Material, PB_OutsourcingBillUpListDTO>> packselect = (a, b, c, d) => new PB_OutsourcingBillUpListDTO
                    {
                        LocationId = b.Id,
                        StorId = b.StorId,
                        AreaId = b.AreaId,
                        RackId = b.RackId,
                        OutsourcingBillId = c.ParentId,
                        OutsourcingBillDetailId = c.Id,
                        MeasureId = d.storageId,
                    };
                    packselect = packselect.BuildExtendSelectExpre();
                    var packdata = from a in Db.GetIQueryable<PB_OutsourcingBillUpList>().AsExpandable()
                                   join b in Db.GetIQueryable<PB_Location>() on a.LocalId equals b.Id into ab
                                   from b in ab.DefaultIfEmpty()
                                   join c in Db.GetIQueryable<PB_OutsourcingBillDetail>() on new { a.ParentId, a.MaterialId } equals new { c.ParentId, c.MaterialId } into ac
                                   from c in ac.DefaultIfEmpty()
                                   join d in Db.GetIQueryable<PB_Material>() on a.MaterialId equals d.Id into ad
                                   from d in ad.DefaultIfEmpty()
                                   select @packselect.Invoke(a, b, c, d);
                    var unloaddata = await packdata.ToListAsync();
                    //删除备货出库的明细
                    sqlMain += @" delete from PB_OutsourcingBillUpList where PackId='" + packid + "'; ";
                    //修改包的状态为入库
                    sqlMain += @" update PB_PackList set Status=3 where Id='" + packid + "'; ";
                    await ExecuteSqlAsync(sqlMain);

                    string strStorId = string.Empty;
                    string strLocalId = string.Empty;
                    string strAreaId = string.Empty;
                    string strRackId = string.Empty;
                    List<IT_LocalDetail> LocalDetaillist = new List<IT_LocalDetail>();
                    sqlMain = "";
                    foreach (var item in unloaddata)
                    {
                        strStorId = item.StorId;
                        strLocalId = item.LocalId;
                        strAreaId = item.AreaId;
                        strRackId = item.RackId;
                        IT_LocalDetail itdata = new IT_LocalDetail();
                        itdata.Id = IdHelper.GetId();
                        itdata.StorId = item.StorId;
                        itdata.LocalId = item.LocalId;
                        itdata.MaterialId = item.MaterialId;
                        itdata.MeasureId = item.MeasureId;
                        itdata.BarCode = pbdata.QrCode;
                        itdata.InTime = DateTime.Now;
                        itdata.CreateTime = DateTime.Now;
                        itdata.CreatorId = _operator.UserId;
                        itdata.Deleted = false;
                        itdata.Num = item.Nums;
                        itdata.PackId = packid;
                        itdata.OldOrNew = pbdata.OldOrNew;
                        itdata.JobId = "";
                        LocalDetaillist.Add(itdata);

                        //减去备货单明细的出库数量
                        sqlMain += @" update PB_OutsourcingBillDetail set ReadyNums=IFNULL(ReadyNums,0)-" + item.Nums + " where ParentId='" + outsourId + "' and MaterialId='" + item.MaterialId + "'; ";

                        if (pbdata.OldOrNew == 0)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)+" + item.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + item.Nums + " where MaterialId='" + item.MaterialId + "' and StorId='" + item.StorId + "' and LocalId='" + item.LocationId + "'; ";
                        }
                        else if (pbdata.OldOrNew == 1)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)+" + item.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + item.Nums + " where MaterialId='" + item.MaterialId + "' and StorId='" + item.StorId + "' and LocalId='" + item.LocationId + "'; ";
                        }
                    }
                    //添加库存汇总
                    await ExecuteSqlAsync(sqlMain);
                    //添加库存明细
                    await Db.InsertAsync<IT_LocalDetail>(LocalDetaillist);

                    //添加PB_Stock
                    PB_Stock stockdata = new PB_Stock();
                    stockdata.Id = IdHelper.GetId();
                    stockdata.PackId = packid;
                    stockdata.StorId = strStorId;
                    stockdata.LocationId = strLocalId;
                    stockdata.StockNumber = pbdata.Nums;
                    stockdata.MaterialId = pbdata.MaterialId;
                    stockdata.CreatorId = _operator.UserId;
                    stockdata.CreatorName = basedata.RealName;
                    stockdata.CreateTime = DateTime.Now;
                    stockdata.OperatorId = _operator.UserId;
                    stockdata.Type = 1;
                    stockdata.OrderID = outsourId;
                    stockdata.OldOrNew = pbdata.OldOrNew;
                    stockdata.AreaId = strAreaId;
                    stockdata.RackId = strRackId;
                    await Db.InsertAsync<PB_Stock>(stockdata);

                    //添加PB_StockLog
                    PB_StockLog stockdatalog = new PB_StockLog();
                    stockdatalog.Id = IdHelper.GetId();
                    stockdatalog.PackId = packid;
                    stockdatalog.StorId = strStorId;
                    stockdatalog.LocationId = strLocalId;
                    stockdatalog.StockNumber = pbdata.Nums;
                    stockdatalog.MaterialId = pbdata.MaterialId;
                    stockdatalog.CreatorId = _operator.UserId;
                    stockdatalog.CreatorName = basedata.RealName;
                    stockdatalog.CreateTime = DateTime.Now;
                    stockdatalog.OperatorId = _operator.UserId;
                    stockdatalog.Type = 1;
                    stockdatalog.OrderID = outsourId;
                    stockdatalog.OldOrNew = pbdata.OldOrNew;
                    stockdatalog.State = 0;
                    await Db.InsertAsync<PB_StockLog>(stockdatalog);
                }
            }
            else if (packmodel == "04")
            {
                //定义一个变量用于存储sql变量
                string sqlMain = string.Empty;
       
                Expression<Func<PB_OutsourcingBillUpList, PB_Location, PB_OutsourcingBillDetail, PB_Material, PB_OutsourcingBillUpListDTO>> packselect = (a, b, c, d) => new PB_OutsourcingBillUpListDTO
                {
                    LocationId = b.Id,
                    StorId = b.StorId,
                    AreaId = b.AreaId,
                    RackId = b.RackId,
                    OutsourcingBillId = c.ParentId,
                    OutsourcingBillDetailId = c.Id,
                    MeasureId = d.storageId,
                };
                packselect = packselect.BuildExtendSelectExpre();
                var packdata = from a in Db.GetIQueryable<PB_OutsourcingBillUpList>().AsExpandable()
                               join b in Db.GetIQueryable<PB_Location>() on a.LocalId equals b.Id into ab
                               from b in ab.DefaultIfEmpty()
                               join c in Db.GetIQueryable<PB_OutsourcingBillDetail>() on  a.ParentId equals c.Id into ac
                               from c in ac.DefaultIfEmpty()
                               join d in Db.GetIQueryable<PB_Material>() on a.MaterialId equals d.Id into ad
                               from d in ad.DefaultIfEmpty()
                               select @packselect.Invoke(a, b, c, d);
                var unloaddata = await packdata.Where(x => x.OutsourcingBillDetailId == outsourId && x.MaterialId == materialId).FirstOrDefaultAsync();
                //删除外协日志明细表
                sqlMain += @" delete from PB_OutsourcingBillUpList where MaterialId='" + unloaddata.MaterialId + "' and ParentId='" + unloaddata.OutsourcingBillDetailId + "' and LocalId='" + unloaddata.LocationId + "' and Nums='" + nums + "'  LIMIT 1; ";
                //减去备货单明细的出库数量
                sqlMain += @" update PB_OutsourcingBillDetail set ReadyNums=IFNULL(ReadyNums,0)-" + nums + " where Id='" + outsourId + "'; ";
                await ExecuteSqlAsync(sqlMain);

                PB_Stock stockdata = new PB_Stock();
                stockdata.Id = IdHelper.GetId();
                stockdata.PackId = packid;
                stockdata.StorId = unloaddata.StorId;
                stockdata.LocationId = unloaddata.LocationId;
                stockdata.StockNumber = nums;
                stockdata.MaterialId = unloaddata.MaterialId;
                stockdata.CreatorId = _operator.UserId;
                stockdata.CreatorName = basedata.RealName;
                stockdata.CreateTime = DateTime.Now;
                stockdata.OperatorId = _operator.UserId;
                stockdata.Type = 1;
                stockdata.OrderID = outsourId;
                stockdata.OldOrNew = 1;
                stockdata.AreaId = unloaddata.AreaId;
                stockdata.RackId = unloaddata.RackId;
                await Db.InsertAsync<PB_Stock>(stockdata);

                PB_StockLog stockdatalog = new PB_StockLog();
                stockdatalog.Id = IdHelper.GetId();
                stockdatalog.PackId = packid;
                stockdatalog.StorId = unloaddata.StorId;
                stockdatalog.LocationId = unloaddata.LocationId;
                stockdatalog.StockNumber = nums;
                stockdatalog.MaterialId = unloaddata.MaterialId;
                stockdatalog.CreatorId = _operator.UserId;
                stockdatalog.CreatorName = basedata.RealName;
                stockdatalog.CreateTime = DateTime.Now;
                stockdatalog.OperatorId = _operator.UserId;
                stockdatalog.Type = 1;
                stockdatalog.OrderID = outsourId;
                stockdatalog.OldOrNew = 1;
                stockdatalog.State = 0;
                await Db.InsertAsync<PB_StockLog>(stockdatalog);

                IT_LocalDetail itdata = new IT_LocalDetail();
                itdata.Id = IdHelper.GetId();
                itdata.StorId = unloaddata.StorId;
                itdata.LocalId = unloaddata.LocationId;
                itdata.MaterialId = unloaddata.MaterialId;
                itdata.MeasureId = unloaddata.MeasureId;
                itdata.BarCode = packqrocde;
                itdata.InTime = DateTime.Now;
                itdata.CreateTime = DateTime.Now;
                itdata.CreatorId = _operator.UserId;
                itdata.Deleted = false;
                itdata.Num = nums;
                itdata.PackId = packid;
                itdata.OldOrNew = 1;
                itdata.JobId = "";
                await Db.InsertAsync<IT_LocalDetail>(itdata);

                sqlMain = "";
                sqlMain = @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)+" + nums + ",ScanNum=IFNULL(ScanNum,0)+" + nums + " where MaterialId='" + unloaddata.MaterialId + "' and StorId='" + unloaddata.StorId + "' and LocalId='" + unloaddata.LocationId + "' ";
                await ExecuteSqlAsync(sqlMain);
            }                             

        }

        /// <summary>
        /// 取消外协备货出库
        /// </summary>
        /// <param name="outsourqrcode"></param>
        /// <returns></returns>
        [Transactional]
        public async Task CancelStockUpAsync(string outsourqrcode)
        {
            string outsourId = string.Empty;//外协单Id
            var models = outsourqrcode.Split(',');
            if (models.Length > 2)
            {
                outsourId = outsourqrcode.Split(',')[2].ToString();
            }
            else
            {
                outsourId = outsourqrcode.Split(',')[1].ToString();
            }


            var qdata = await Db.GetIQueryable<PB_OutsourcingBillUpList>().Where(x => x.ParentId == outsourId).ToListAsync();


            var listdetdata = qdata.GroupBy(x => new { x.MaterialId, x.Nums, x.LocalId }).Select(x => new { MaterialId = x.Key.MaterialId, StockVolume = x.Key.Nums, LocationId = x.Key.LocalId }).ToList();
            string sqlMains = string.Empty;
            foreach (var item in listdetdata)
            {
                sqlMains += @" update IT_LocalMaterial set Nums_R=IFNULL(Nums_R,0)+" + item.StockVolume + " where MaterialId='" + item.MaterialId + "' and LocalId='" + item.LocationId + "'; ";
            }
            //取消备货单提交后恢复库存备用单占用量
            await ExecuteSqlAsync(sqlMains);

            //获取出库单信息
            TD_OutStorage outdata = await Db.GetIQueryable<TD_OutStorage>().Where(x => x.RefCode == outsourId).FirstOrDefaultAsync();
            //删除出库单信息
            sqlMains = "";
            sqlMains = @" delete from TD_OutStorRecord where OutStorId='" + outdata.Id + "'; delete from TD_OutStorDetail where OutStorId='" + outdata.Id + "'; delete from TD_OutStorage where Id='" + outdata.Id + "';    ";
            await ExecuteSqlAsync(sqlMains);
            //修改备货单状态为开始作业
            string sqlstr = @"update PB_OutsourcingBillDetail set State=2 where Id='" + outsourId + "'";
            await ExecuteSqlAsync(sqlstr);

        }


        #endregion

        #region 外协扫描包
        /// <summary>
        /// 扫描包获取该流转卡
        /// </summary>
        /// <param name="packqrcode">包二维码</param>
        /// <returns></returns>
        [Transactional]
        public async Task<PB_CirculationBillDTO> GetPackDataListAsync(string packqrcode)
        {
            //包编号ID
            string packid = string.Empty;
            string packmodel = packqrcode.Split(',')?[0].ToString();
            if (packmodel == "13")
            {
                packid = packqrcode.Split(',')?[1].ToString();
            }
            else
            {
                throw new BusException("请扫描或输入正确的物料包二维码!");
            }
            //用户信息
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            //根据包获取对应的外协任务单
            Expression<Func<PB_OutsourcingBill, PB_CirculationBill, PB_PickOutsourcingBillDataDTO>> select = (a, c) => new PB_PickOutsourcingBillDataDTO
            {
                PackId = c.PackId,
                CirculationId = c.Id
            };

            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_OutsourcingBill>().AsExpandable()
                    join c in Db.GetIQueryable<PB_CirculationBill>() on a.Id equals c.TaskId into ac
                    from c in ac.DefaultIfEmpty()
                    select @select.Invoke(a, c);
            var mainList = await q.Where(x => x.PackId == packid).FirstOrDefaultAsync();
            if (mainList == null)
            {
                throw new BusException("暂未查到该包信息!");
            }
             if (mainList.Status != 6)
            {
                throw new BusException("改订单的状态还未开始作业!");
            }
            //获取该包对应的流转单
            PB_CirculationBill data = await Db.GetIQueryable<PB_CirculationBill>().Where(s => s.PackId == packid &&s.TaskId == mainList.Id).FirstOrDefaultAsync();
            //修改姓名和时间
            WorkProject workProject = data.Reamrk.ToObject<WorkProject>();
            workProject.UserName = userdata.RealName;
            workProject.Date = DateTime.Now.ToString("yyyy-MM-dd");
            //修改操作
            List<string> props = new List<string>() { "Reamrk" };
            data.Reamrk = workProject.ToJson();
            await Db.UpdateAsync(data, props);

            //获取工序报工物料信息
            Expression<Func<PB_CirculationBill, PB_Material, PB_CirculationBillDTO>> selects = (a, b) => new PB_CirculationBillDTO
            {
                MaterialName = b.Name,
                MaterialCode = b.Code,
                MaterialSpec = b.Spec,
                ReceivingNums = a.StatisticalQuantity - a.ReceivingQuantity
            };
            selects = selects.BuildExtendSelectExpre();
            var w = from a in Db.GetIQueryable<PB_CirculationBill>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @selects.Invoke(a, b);
            var data1 = await w.Where(x => x.Id == mainList.CirculationId).FirstOrDefaultAsync();
            return data1;

        }

        /// <summary>
        /// 打印外包订单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<PB_OutsourcingBillInfo> GetOutsourcingBillInfo(string id)
        {
            Expression<Func<PB_OutsourcingBill, PB_Customer,PB_Project, PB_ProductionOrder, PB_OutsourcingBillInfo>> select = (a, b,c,d) => new PB_OutsourcingBillInfo
            {
              OutsourcingCompanyName=b.Name,
              ProjectName=c.Name,
              POName=d.POName,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_OutsourcingBill>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Customer>() on a.OutsourcingCompanyId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Project>() on a.ProjectId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_ProductionOrder>() on a.RefCode equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b,c,d);
            var mainList = await q.Where(x => x.Id == id).FirstOrDefaultAsync();
            Expression<Func<PB_OutsourcingBillDetail, PB_Material, PB_OutsourcingBillDetailList>> subs = (a, b) => new PB_OutsourcingBillDetailList
            {
               MaterCode=b.Code,
               MaterName=b.Name,
               MaterSpec=b.Spec,
            };
            subs = subs.BuildExtendSelectExpre();
            var sub = from a in Db.GetIQueryable<PB_OutsourcingBillDetail>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select subs.Invoke(a, b);
            var Subdata = await sub.Where(x => x.ParentId == id).ToListAsync();
            PB_OutsourcingBillInfo pB_OutsourcingBillInfo = new PB_OutsourcingBillInfo();
            pB_OutsourcingBillInfo.Id = mainList.Id;
            pB_OutsourcingBillInfo.OutsourcingCompanyName = mainList.OutsourcingCompanyName;
            pB_OutsourcingBillInfo.ProjectName = mainList.ProjectName;
            pB_OutsourcingBillInfo.OutsourcingDate = mainList.OutsourcingDate;
            pB_OutsourcingBillInfo.TheoreticalWT = mainList.TheoreticalWT;
            pB_OutsourcingBillInfo.Weight = mainList.Weight;
            pB_OutsourcingBillInfo.SubData = Subdata;
            return pB_OutsourcingBillInfo;
        }


        #endregion

        #region 外包回货签收
        /// <summary>
        /// 外包回货签收反填明细
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<PB_ProductionDataListDTO> GetTheDataInfoSignAsync(string id)
        {
            PB_ProductionDataListDTO pblist = new PB_ProductionDataListDTO();
            PB_OutsourcingBill Datalist = await Db.GetIQueryable<PB_OutsourcingBill>().Where(x => x.Id == id).FirstOrDefaultAsync();
            //将父类对象赋值转换成子类对象
            pblist = AutoAdaptation.AutoCopy<PB_OutsourcingBill, PB_ProductionDataListDTO>(Datalist);

            string sqlMain = $"SELECT *,(Qty - SignedQuantity) AS Quantity FROM(select a.Id, a.MaterialId,b.`Code` AS Code, b.`Name` AS Name, b.Spec AS Spec, a.Weight , a.Qty, a.Remark, SUM(IFNULL(d.RecNum, 0)) AS SignedQuantity from PB_OutsourcingBillDetail AS a JOIN PB_Material AS b ON a.MaterialId = b.Id    LEFT JOIN TD_Receiving AS c ON a.ParentId = c.OrderId   LEFT JOIN TD_RecDetail AS d ON c.Id = d.RecId AND a.MaterialId = d.MaterialId WHERE a.ParentId = '{id}' GROUP BY    a.Id, a.MaterialId, b.`Code`, b.`Name`, b.Spec, a.Qty, a.Remark) AS t; ";
            List<SignOutsourcingBillDetailDTO> Signlist = await Db.GetListBySqlAsync<SignOutsourcingBillDetailDTO>(sqlMain);

            pblist.SignOutsourcingBillDetail = Signlist;

            return pblist;
            //throw new NotImplementedException();
        }

        /// <summary>
        /// 外包回货签收,并且生成收货单
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [Transactional]
        public async Task UpdateDataAsync(PB_ProductionDataListDTO data)
        {
            List<SignOutsourcingBillDetailDTO> signOutsourcings = data.SignOutsourcingBillDetail;
            //获取用户信息
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();


            
            //SQL变量
            string sql = string.Empty;
            //收货单Id
            string receId = IdHelper.GetId();
            List<TD_RecDetail> detaillist = new List<TD_RecDetail>();
            double dnum = 0;
            int index = 0;   //index=1部分签收;index=2已签收
            foreach (var item in signOutsourcings)
            {
                double itemnums = Convert.ToDouble(item.Quantity + item.SignedQuantity);
                //如果多签收也是显示全部签收
                if (item.Qty <= itemnums )
                {
                    if (index != 1)
                    {
                        index = 2;
                    }
                }
                else
                {
                    index = 1;
                }
                if (item.Quantity > 0)
                {
                    TD_RecDetail trd = new TD_RecDetail();
                    trd.Id = IdHelper.GetId();
                    trd.RecId = receId;
                    trd.StorId = data.StorId;
                    trd.MaterialId = item.MaterialId;
                    trd.Price = Convert.ToDouble(item.Price);
                    trd.PlanNum = Convert.ToDouble(item.Quantity);
                    trd.RecNum = Convert.ToDouble(item.Quantity);
                    trd.InNum = 0;
                    trd.Amount = Convert.ToDouble(item.Quantity * item.Price);
                    trd.CreateTime = DateTime.Now;
                    trd.CreatorId = _operator.UserId;
                    trd.Deleted = false;
                    trd.TaskId = data.Id;
                    detaillist.Add(trd);
                    dnum += Convert.ToDouble(item.Quantity);

                    //double numbers = Convert.ToDouble(item.Quantity);
                    //更新打印列表
                    var PackList = await Db.GetIQueryable<PB_PackList>().Where(x => x.TaskId == data.Id && x.MaterialId == item.MaterialId).ToListAsync();

                    List<string> PackdataList =  PackList.Select(x => x.Id).ToList();
                    string packIds = "";
                    foreach (var item2 in PackdataList)
                    {
                        packIds += "'" + item2 + "',";
                    }
                    packIds = packIds.Substring(0, packIds.Length - 1);
                    if (PackList.Count > 0)
                    {

                        int Materialcount = await Db.GetIQueryable<PB_JobBookingQrCode>().Where(x => PackdataList.Contains(x.PackId)  && x.MaterialId == item.MaterialId && x.RecId =="").CountAsync();
                        if (Materialcount < item.Quantity)
                        {
                            throw new BusException("签收数量大于回货数量,请重新签收");
                        }

                        sql += @" UPDATE PB_JobBookingQrCode SET RecId = '" + receId + "' where PackId IN (" + packIds + ") AND( RecId = '' OR RecId IS NULL ) LIMIT " + item.Quantity + "; ";

                    }
                }
            }

            
            await ExecuteSqlAsync(sql);

            PB_OutsourcingBill pbdata = await Db.GetIQueryable<PB_OutsourcingBill>().Where(x => x.Id == data.Id).FirstOrDefaultAsync();
            TD_Receiving recedata = new TD_Receiving();
            recedata.Id = receId;
            recedata.StorId = data.StorId;
            recedata.Code = GetTimeStamp();
            recedata.OrderTime = DateTime.Now;
            recedata.RecTime = DateTime.Now;
            recedata.Type = "HalfProduct";
            recedata.RefCode = "";
            recedata.Status = 0;
            recedata.SupId = pbdata.OutsourcingCompanyId; //外协单位ID
            recedata.TotalNum = dnum;
            recedata.InNum = 0;
            recedata.TotalAmt = Convert.ToDouble(pbdata.Amount);  //金额
            recedata.CreateTime = DateTime.Now;
            recedata.CreatorId = _operator.UserId;
            recedata.Deleted = false;
            recedata.QrCode = h_TdRe + "," + receId;
            recedata.DeptId = userdata.DepartmentId;
            recedata.UpdateTime = DateTime.Now;
            recedata.OrderId = data.Id;


            await Db.InsertAsync<TD_RecDetail>(detaillist);
            await Db.InsertAsync<TD_Receiving>(recedata);

            //修改外采订单状态
            string sqlMain = string.Empty;
            if (index == 2)
            {
                sqlMain = @" update PB_OutsourcingBill set `Status`=9 where Id='" + data.Id + "' ";
            }
            else if (index == 1)
            {
                sqlMain = @" update PB_OutsourcingBill set `Status`=8 where Id='" + data.Id + "' ";
            }
            await ExecuteSqlAsync(sqlMain);

        }



        #endregion

        #region  建立外包单获取物料信息

        /// <summary>
        /// 获取物料信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<OutBillMaterialDTO>> GetOutBillMaterialListAsync(PageInput<OutBillMaterialConditionDTO> input)
        {
            var search = input.Search;
            if (search.OrderId.IsNullOrEmpty())
            {
                string sqlMain = @"select * from (select a.Id,a.MaterialId,b.`Code` as MaterialCode,b.`Name` as MaterialName,b.Spec as MaterialSpec,b.ProductCode,b.Price,b.Weight,IFNULL(a.ScanNum,0) as SacnNums,IFNULL(a.LocalId,'') as LocalId,IFNULL(a.StorId,'') as StorId,IFNULL(d.`Name`,'') as StorName,IFNULL(c.StorAreaName,'') as LocalName,b.UsageStatus from IT_LocalMaterial as a
left join PB_Material as b on a.MaterialId=b.Id
left join View_StorAreaRackLocation as c on a.LocalId=c.Id
left join Base_Department as d on a.StorId=d.Id
) as t where SacnNums>0 and UsageStatus=1 ";
                if (!search.StorId.IsNullOrEmpty())
                {
                    sqlMain += @" and StorId='" + search.StorId + "' ";
                }
                if (!search.Keyword.IsNullOrEmpty())
                {
                    sqlMain += @" and (MaterialCode like '%"+ search.Keyword + "%' or MaterialName like '%" + search.Keyword + "%' or ProductCode like '%" + search.Keyword + "%') ";
                }
                var lidata = await Db.GetListBySqlAsync<OutBillMaterialDTO>(sqlMain);
                int num = lidata.Count;
                var list = lidata
                   .Skip((input.PageIndex - 1) * input.PageRows)
                   .Take(input.PageRows).ToList();
                return new PageResult<OutBillMaterialDTO> { Data = lidata, Total = num };

                //Expression<Func<PB_Material, IT_LocalMaterial, View_StorAreaRackLocation, Base_Department, OutBillMaterialDTO>> select = (a, b, c, d) => new OutBillMaterialDTO
                //{

                //};
                //select = select.BuildExtendSelectExpre();
                //var q = from a in Db.GetIQueryable<PB_Material>().AsExpandable()
                //        join b in Db.GetIQueryable<IT_LocalMaterial>() on a.Id equals b.MaterialId into ab
                //        from b in ab.DefaultIfEmpty()
                //        join c in Db.GetIQueryable<View_StorAreaRackLocation>() on b.LocalId equals c.Id into bc
                //        from c in bc.DefaultIfEmpty()
                //        join d in Db.GetIQueryable<Base_Department>() on b.StorId equals d.Id into bd
                //        from d in bd.DefaultIfEmpty()
                //        select @select.Invoke(a, b, c, d);

            }
            else
            {
                string sqlMain = @"select * from (
select b.Id,a.ParentId as OrderId,a.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,c.ProductCode,c.Price,c.Weight,IFNULL(c.ScanNum,0) as SacnNums,a.Productionquantity,IFNULL(b.StorId,'') as StorId,IFNULL(e.`Name`,'') as StorName,IFNULL(b.LocalId,'') as LocalId,IFNULL(d.StorAreaName,'') as LocalName,c.UsageStatus from PB_ProductionOrderDetail as a
left join IT_LocalMaterial as b on a.MaterialId=b.MaterialId
left join PB_Material as c on a.MaterialId=c.Id
left join View_StorAreaRackLocation as d on b.LocalId=d.Id
left join Base_Department as e on b.StorId=e.Id
where a.ParentId='" + search.OrderId + "' ) as t where SacnNums>0 and UsageStatus=1  ";
                if (!search.StorId.IsNullOrEmpty())
                {
                    sqlMain += @" and StorId='" + search.StorId + "' ";
                }
                if (!search.Keyword.IsNullOrEmpty())
                {
                    sqlMain += @" and (MaterialCode like '%" + search.Keyword + "%' or MaterialName like '%" + search.Keyword + "%' or ProductCode like '%" + search.Keyword + "%')";
                }
                var lidata = await Db.GetListBySqlAsync<OutBillMaterialDTO>(sqlMain);
                int num = lidata.Count;
                var list = lidata
                   .Skip((input.PageIndex - 1) * input.PageRows)
                   .Take(input.PageRows).ToList();
                return new PageResult<OutBillMaterialDTO> { Data = lidata, Total = num };
            }
        }

        #endregion

        #region 外包单

        #region PDA外包单领料

        /// <summary>
        /// PDA出库选择物料信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<OutBillMaterialReceiveDTO>> GetOutBillMaterialReceiveListAsync(PageInput<OutBillMaterialReceiveConditionDTO> input)
        {
            var search = input.Search;
            string localId = search.LocalQrCode.Split(',')?[1].ToString();
            string outbillId = search.OutBillQrCode.Split(',')?[1].ToString();
            string sql = @"select *,(OutBillNums-IFNULL(OytNums,0)) as SurplusNums from ( select a.Id,a.ParentId as OutBillId,a.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,c.ProductCode,b.LocalId,b.ScanNum,a.Qty as OutBillNums,(select SUM(Nums) from PB_OutsourcingBillUpList where ParentId='{0}' and MaterialId=a.MaterialId) as OytNums from PB_OutsourcingBillDetail as a
left join IT_LocalMaterial as b on a.MaterialId=b.MaterialId
left join PB_Material as c on a.MaterialId=c.Id
where a.ParentId='{0}' and b.LocalId='{1}') as t where 1=1  ";
            if (!search.Keyword.IsNullOrEmpty())
            {
                sql += @" and (MaterialCode like '%"+ search.Keyword + "%' or MaterialName like '%" + search.Keyword + "%' or ProductCode like '%" + search.Keyword + "%') ";
            }
            string sqlMain = string.Format(sql, outbillId, localId);
            var lidata = await Db.GetListBySqlAsync<OutBillMaterialReceiveDTO>(sqlMain);
            int num = lidata.Count;
            var list = lidata
               .Skip((input.PageIndex - 1) * input.PageRows)
               .Take(input.PageRows).ToList();
            return new PageResult<OutBillMaterialReceiveDTO> { Data = lidata, Total = num };
        }

        /// <summary>
        /// PDA物料出库保存
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [Transactional]
        public async Task SaveOutBillMaterialReceiveDataAsync(OutBillMaterialDataDTO data)
        {
            //外包单Id
            string outbillId = data.OutBillQrCode.Split(',')?[1].ToString();
            //货位Id
            string LocalId = data.LocalQrCode.Split(',')?[1].ToString();

            PB_Location locadata = await Db.GetIQueryable<PB_Location>().Where(x => x.Id == LocalId).FirstOrDefaultAsync();

            //获取当前货位的物料数量
            List<IT_LocalDetail> LocalDetail = await Db.GetIQueryable<IT_LocalDetail>().Where(x => x.MaterialId == data.OutBillMaterialReceive.MaterialId && x.LocalId == LocalId).ToListAsync();
            if (LocalDetail.Count == 0)
            {
                throw new BusException("请检查当前货位物料是否存在");
            }
            //汇总货位物料数量,并和本次物料出库数量做对比
            var LocalDetaildata = LocalDetail.GroupBy(x => new { x.MaterialId, x.LocalId }).Select(x => new { MaterialId = x.Key.MaterialId, LocalId = x.Key.LocalId, Nums = x.Sum(i => i.Num) }).FirstOrDefault();
            if (LocalDetaildata.Nums < data.OutBillMaterialReceive.Nums)
            {
                throw new BusException("请检查当前货位物料是否满足");
            }
            //获取要出库的物料数量
            double? warehouseNum = data.OutBillMaterialReceive.Nums;
            string sqlMain = string.Empty;
            //减库存
            foreach (var item in LocalDetail)
            {
                if (warehouseNum > 0)
                {
                    //出库数量等于库存明细数量时
                    if (item.Num == warehouseNum)
                    {
                        warehouseNum -= item.Num;
                        if (item.OldOrNew == 0)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                        }
                        else if (item.OldOrNew == 1)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                        }
                        sqlMain += @" delete from IT_LocalDetail where Id='" + item.Id + "'; ";

                    }
                    else if (item.Num < warehouseNum)   //出库数量大于库存明细数量时
                    {
                        if (warehouseNum >= item.Num)
                        {
                            warehouseNum -= item.Num;
                            if (item.OldOrNew == 0)
                            {
                                sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                            }
                            else if (item.OldOrNew == 1)
                            {
                                sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                            }
                            sqlMain += @" delete from IT_LocalDetail where Id='" + item.Id + "'; ";
                        }
                        else
                        {
                            if (item.OldOrNew == 0)
                            {
                                sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                            }
                            else if (item.OldOrNew == 1)
                            {
                                sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                            }
                            sqlMain += @" update IT_LocalDetail set Num=IFNULL(Num,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                            warehouseNum -= warehouseNum;
                        }
                    }
                    else if (item.Num > warehouseNum) //出库数量小于库存明细数量时
                    {
                        item.Num -= warehouseNum;
                        if (item.OldOrNew == 0)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                        }
                        else if (item.OldOrNew == 1)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                        }
                        sqlMain += @" update IT_LocalDetail set Num=IFNULL(Num,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                        warehouseNum -= warehouseNum;
                    }
                }
                else
                {
                    continue;
                }

            }

            //物料档案减去审核前数量
            sqlMain += @" update PB_Material set ScanNum=IFNULL(ScanNum,0)-" + data.OutBillMaterialReceive.Nums + " where Id='" + data.OutBillMaterialReceive.MaterialId + "'; ";

            await Db.ExecuteSqlAsync(sqlMain);

            //物料信息
            PB_Material Material = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == data.OutBillMaterialReceive.MaterialId).FirstOrDefaultAsync();

            //登录人信息
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            //增加出库日志
            PB_StockLog pblog = new PB_StockLog();
            pblog.Id = IdHelper.GetId();
            pblog.PackId = "";
            pblog.StorId = locadata.StorId;
            pblog.LocationId = LocalId;
            pblog.MaterialId = data.OutBillMaterialReceive.MaterialId;
            pblog.StockNumber = data.OutBillMaterialReceive.Nums;
            pblog.CreatorId = _operator.UserId;
            pblog.CreatorName = userdata.RealName;
            pblog.OperatorId = _operator.UserId;
            pblog.Type = 1;
            pblog.OrderID = outbillId;
            pblog.CreateTime = DateTime.Now;
            pblog.State = 1;
            pblog.JobId = "";
            await Db.InsertAsync<PB_StockLog>(pblog);

            PB_OutsourcingBillUpList billuplist = new PB_OutsourcingBillUpList();
            billuplist.Id = IdHelper.GetId();
            billuplist.ParentId = outbillId;
            billuplist.LocalId = LocalId;
            billuplist.MaterialId = data.OutBillMaterialReceive.MaterialId;
            billuplist.Nums = data.OutBillMaterialReceive.Nums;
            billuplist.CreatorId = _operator.UserId;
            billuplist.CreateTime = DateTime.Now;
            billuplist.CreatorRealName = userdata.RealName;
            billuplist.Deleted = false;
            billuplist.CirBillId = "";
            await Db.InsertAsync<PB_OutsourcingBillUpList>(billuplist);

        }

        /// <summary>
        /// 获取外包单已出库明细
        /// </summary>
        /// <param name="OutBillQrCode">外包单二维码</param>
        /// <returns></returns>
        public async Task<List<OutsourcingBillUpListDTO>> GetOutsourcingBillUpListAsync(string OutBillQrCode)
        {
            //外包单Id
            string outbillId = OutBillQrCode.Split(',')?[1].ToString();

            Expression<Func<PB_OutsourcingBillUpList, View_StorAreaRackLocation, PB_Material, OutsourcingBillUpListDTO>> packselect = (a, b, c) => new OutsourcingBillUpListDTO
            {
                LocalName = b.StorAreaName,
                MaterialCode = c.Code,
                MaterialName = c.Name,
                MaterialSpec = c.Spec,
                ProductCode = c.ProductCode
            };
            packselect = packselect.BuildExtendSelectExpre();
            var packdata = from a in Db.GetIQueryable<PB_OutsourcingBillUpList>().AsExpandable()
                           join b in Db.GetIQueryable<View_StorAreaRackLocation>() on a.LocalId equals b.Id into ab
                           from b in ab.DefaultIfEmpty()
                           join c in Db.GetIQueryable<PB_Material>() on a.MaterialId equals c.Id into ac
                           from c in ac.DefaultIfEmpty()
                           select @packselect.Invoke(a, b, c);
            return await packdata.Where(x => x.ParentId == outbillId).ToListAsync();
        }

        /// <summary>
        /// 移除出库明细
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [Transactional]
        public async Task DeleteOutsourcingBillUpListAsync(OutsourcingBillUpListDTO data)
        {
            string sqlMain = string.Empty;
            //查询库存汇总表信息
            IT_LocalMaterial LocalMaterial = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == data.MaterialId && x.LocalId == data.LocalId).FirstOrDefaultAsync();

            PB_Location locadata = await Db.GetIQueryable<PB_Location>().Where(x => x.Id == data.LocalId).FirstOrDefaultAsync();

            List<IT_LocalMaterial> LocalMaterialList = new List<IT_LocalMaterial>();
            if (LocalMaterial != null)
            {
                sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)+" + data.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + data.Nums + " where MaterialId='" + data.MaterialId + "' and LocalId='" + data.LocalId + "'; ";
            }
            else
            {
                IT_LocalMaterial LocalMaterialData = new IT_LocalMaterial();
                LocalMaterialData.Id = IdHelper.GetId();
                LocalMaterialData.MaterialId = data.MaterialId;
                LocalMaterialData.StorId = locadata.StorId;
                LocalMaterialData.LocalId = data.LocalId;
                LocalMaterialData.Nums_N = 0;
                LocalMaterialData.Nums_O = Convert.ToDouble(data.Nums);
                LocalMaterialData.Nums_R = 0;
                LocalMaterialData.CreatorId = _operator.UserId;
                LocalMaterialData.CreateTime = DateTime.Now;
                LocalMaterialData.Deleted = false;
                LocalMaterialData.ScanNum = Convert.ToDouble(data.Nums);
                LocalMaterialData.CheckNum = 0;
                LocalMaterialList.Add(LocalMaterialData);
            }
            //获取物料信息
            PB_Material Material = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == data.MaterialId).FirstOrDefaultAsync();

            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            //添加库存明细
            IT_LocalDetail LocalDetail = new IT_LocalDetail();
            LocalDetail.Id = IdHelper.GetId();
            LocalDetail.StorId = locadata.StorId;
            LocalDetail.LocalId = data.LocalId;
            LocalDetail.MaterialId = data.MaterialId;
            LocalDetail.MeasureId = Material.storageId;
            LocalDetail.BarCode = h_Material + "," + data.MaterialId;
            LocalDetail.InTime = DateTime.Now;
            LocalDetail.Amount = Material.Price * data.Nums;
            LocalDetail.CreateTime = DateTime.Now;
            LocalDetail.CreatorId = _operator.UserId;
            LocalDetail.Price = Material.Price;
            LocalDetail.Deleted = false;
            LocalDetail.Num = data.Nums;
            LocalDetail.PackId = "";
            LocalDetail.OldOrNew = 0;
            LocalDetail.RecId = "";
            LocalDetail.JobId = "";

            PB_StockLog log = new PB_StockLog();
            log.Id = IdHelper.GetId();
            log.PackId = "";
            log.MaterialId = data.MaterialId;
            log.StorId = locadata.StorId;
            log.LocationId = data.LocalId;
            log.StockNumber = data.Nums;
            log.CreatorId = _operator.UserId;
            log.Deleted = false;
            log.CreatorName = userdata.RealName;
            log.OperatorId = _operator.UserId;
            log.Type = 1;
            log.OrderID = data.ParentId;
            log.CreateTime = DateTime.Now;
            log.State = 0;
            log.OldOrNew = 0;
            log.CirBillId = "";
            log.JobId = "";

            sqlMain += " delete from PB_OutsourcingBillUpList where Id='" + data.Id + "'; ";
            sqlMain += @" update PB_Material set ScanNum=IFNULL(ScanNum,0)+" + data.Nums + " where Id='" + data.MaterialId + "'; ";

            await Db.InsertAsync<IT_LocalDetail>(LocalDetail);

            await Db.InsertAsync<IT_LocalMaterial>(LocalMaterialList);

            await Db.InsertAsync<PB_StockLog>(log);

            await Db.ExecuteSqlAsync(sqlMain);

        }

        /// <summary>
        /// 提交外包单出库信息
        /// </summary>
        /// <param name="OutBillQrCode">外包单二维码</param>
        /// <returns></returns>
        [Transactional]
        public async Task SubmitOutsourcingBillUpListAsync(string OutBillQrCode)
        {
            //外包单Id
            string outbillId = OutBillQrCode.Split(',')?[1].ToString();

            string sqlMain = string.Empty;

            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            //获取外包单信息
            PB_OutsourcingBill OutsourcingBill = await Db.GetIQueryable<PB_OutsourcingBill>().Where(x => x.Id == outbillId).FirstOrDefaultAsync();
            PB_ProductionOrder ProductionOrder = new PB_ProductionOrder();
            if (OutsourcingBill != null)
            {
                ProductionOrder = await Db.GetIQueryable<PB_ProductionOrder>().Where(x => x.Id == OutsourcingBill.RefCode).FirstOrDefaultAsync();
            }

            //获取外包单明细
            List<PB_OutsourcingBillDetail> OutsourcingBillDetail = await Db.GetIQueryable<PB_OutsourcingBillDetail>().Where(x => x.ParentId == outbillId).ToListAsync();

            //获取外包单出库明细
            Expression<Func<PB_OutsourcingBillUpList, PB_Location, PB_Material, OutsourcingMaterialDTO>> packselect = (a, b, c) => new OutsourcingMaterialDTO
            {
                StorId = b.StorId,
                Price = c.Price
            };
            packselect = packselect.BuildExtendSelectExpre();
            var packdata = from a in Db.GetIQueryable<PB_OutsourcingBillUpList>().AsExpandable()
                           join b in Db.GetIQueryable<PB_Location>() on a.LocalId equals b.Id into ab
                           from b in ab.DefaultIfEmpty()
                           join c in Db.GetIQueryable<PB_Material>() on a.MaterialId equals c.Id into ac
                           from c in ac.DefaultIfEmpty()
                           select @packselect.Invoke(a, b, c);
            List<OutsourcingMaterialDTO> OutsourcingBillUpList = await packdata.Where(x => x.ParentId == outbillId).ToListAsync();

            var BillUpList = OutsourcingBillUpList.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).ToList();

            foreach (var item in OutsourcingBillDetail)
            {
                var itemdata = BillUpList.Where(x => x.MaterialId == item.MaterialId).FirstOrDefault();
                if (itemdata != null)
                {
                    if (itemdata.Nums != item.Qty)
                    {
                        sqlMain += @" update PB_OutsourcingBillDetail set ReadyNums='" + itemdata.Nums + "',State=2 where Id='" + item.Id + "'; ";
                    }
                    else
                    {
                        sqlMain += @" update PB_OutsourcingBillDetail set ReadyNums='" + itemdata.Nums + "',State=1 where Id='" + item.Id + "'; ";
                    }
                }
                else
                {
                    sqlMain += @" update PB_OutsourcingBillDetail set ReadyNums=0,State=1 where Id='" + item.Id + "'; ";
                }
            }

            //修改外包单状态为待装车
            sqlMain += @" update PB_OutsourcingBill set Status=2 where Id='" + outbillId +"'; ";

            List<TD_OutStorDetail> OutStorDetail = new List<TD_OutStorDetail>();
            List<TD_OutStorRecord> OutStorRecord = new List<TD_OutStorRecord>();
            List<TD_OutStorage> OutStorage = new List<TD_OutStorage>();

            //出库单Id
            string outId = IdHelper.GetId();

            foreach (var item in OutsourcingBillUpList)
            {
                //出库单明细
                TD_OutStorDetail outdetail = new TD_OutStorDetail();
                outdetail.Id = IdHelper.GetId();
                outdetail.StorId = item.StorId;
                outdetail.OutStorId = outId;
                outdetail.LocalId = item.LocalId;
                outdetail.MaterialId = item.MaterialId;
                outdetail.Price = Convert.ToDouble(item.Price);
                outdetail.TotalAmt = Convert.ToDouble(item.Price * item.Nums);
                outdetail.OutNum = Convert.ToDouble(item.Nums);
                outdetail.CreateTime = DateTime.Now;
                outdetail.CreatorId = _operator.UserId;
                outdetail.Deleted = false;
                outdetail.OldOrNew = 0;
                OutStorDetail.Add(outdetail);
            }

            var StorRecord = OutStorDetail.GroupBy(x => new { x.MaterialId, x.StorId, x.Price }).Select(x => new { MaterialId = x.Key.MaterialId, StorId = x.Key.StorId, Price = x.Key.Price, Nums = x.Sum(i => i.OutNum) }).ToList();
            double? doutnums = 0;
            double? dTotalAmt = 0;
            foreach (var item in StorRecord)
            {
                //出库单明细汇总
                TD_OutStorRecord outrecord = new TD_OutStorRecord();
                outrecord.Id = IdHelper.GetId();
                outrecord.StorId = item.StorId;
                outrecord.OutStorId = outId;
                outrecord.MaterialId = item.MaterialId;
                outrecord.Price = Convert.ToDouble(item.Price);
                outrecord.TotalAmt = Convert.ToDouble(item.Price * item.Nums);
                outrecord.OutNum = Convert.ToDouble(item.Nums);
                outrecord.CreateTime = DateTime.Now;
                outrecord.CreatorId = _operator.UserId;
                outrecord.Deleted = false;
                OutStorRecord.Add(outrecord);

                doutnums += item.Nums;
                dTotalAmt += Convert.ToDouble(item.Price * item.Nums);
            }

            //根据领料获取仓库
            var stordata = StorRecord.GroupBy(x => new { x.StorId }).Select(x => new { StorId = x.Key.StorId }).FirstOrDefault();

            //出库单
            TD_OutStorage Storage = new TD_OutStorage();
            Storage.Id = outId;
            Storage.StorageId = stordata.StorId;
            Storage.Code = GetTimeStamp();
            Storage.OutTime = DateTime.Now;
            Storage.OutType = "OutSourcing";
            Storage.RefCode = outId;
            Storage.OutNum = doutnums;
            Storage.TotalAmt = dTotalAmt;
            Storage.Status = 0;
            Storage.Remarks = "";
            Storage.CreateTime = DateTime.Now;
            Storage.CreatorId = _operator.UserId;
            Storage.Deleted = false;
            Storage.QrCode = h_OutStorage + "," + outId;
            Storage.DepId = userdata.DepartmentId;
            Storage.ProjectId = OutsourcingBill.ProjectId;
            Storage.DesignId = ProductionOrder == null ? "" : ProductionOrder.DesignId;
            Storage.FactoryId = userdata.FactoryId;
            OutStorage.Add(Storage);

            await Db.InsertAsync<TD_OutStorDetail>(OutStorDetail);

            await Db.InsertAsync<TD_OutStorRecord>(OutStorRecord);

            await Db.InsertAsync<TD_OutStorage>(OutStorage);

            await Db.ExecuteSqlAsync(sqlMain);
        }

        #endregion

        #region 流转卡生成外包单

        /// <summary>
        /// 获取流转卡物料信息
        /// </summary>
        /// <param name="CirBillQrCode">流转卡二维码</param>
        /// <returns></returns>
        public async Task<OutBillCirculationMaterialDTO> GetOutBillCirculationMaterialAsync(string CirBillQrCode)
        {
            //流转卡Id
            string cirbillId = CirBillQrCode.Split(',')?[1].ToString();
            Expression<Func<PB_CirculationBill, PB_Material, PB_DispatchDetail, OutBillCirculationMaterialDTO>> packselect = (a, b, c) => new OutBillCirculationMaterialDTO
            {
                MaterialName = b.Name,
                MaterialCode = b.Code,
                MaterialSpec = b.Spec,
                ProjectId = c.ProjectId,
                Weight = b.Weight == null ? 0 : b.Weight
            };
            packselect = packselect.BuildExtendSelectExpre();
            var packdata = from a in Db.GetIQueryable<PB_CirculationBill>().AsExpandable()
                           join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                           from b in ab.DefaultIfEmpty()
                           join c in Db.GetIQueryable<PB_DispatchDetail>() on a.DispatchDetailId equals c.Id into ac
                           from c in ac.DefaultIfEmpty()
                           select @packselect.Invoke(a, b, c);
            OutBillCirculationMaterialDTO res = await packdata.Where(x => x.Id == cirbillId).FirstOrDefaultAsync();
            if (res != null)
            {
                if (res.StatisticalQuantity == 0)
                {
                    throw new BusException("流转卡未统计");
                }
            }
            if (res == null)
            {
                throw new BusException("请检查流转卡是否存在");
            }
            return res;
        }

        /// <summary>
        /// 保存流转卡生成外包单
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [Transactional]
        public async Task SaveOutBillMaterialAsync(CirBillSaveOutBill data)
        {
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            //获取当前外协单位
            List<PB_OutsourcingBill> OutsourcingBilllist = await Db.GetIQueryable<PB_OutsourcingBill>().Where(x => x.OutsourcingCompanyId == data.OutsourcingCompanyId && x.OutsourcingDate == data.OutsourcingDate).ToListAsync();

            //流转卡信息按照任务单分组
            var cirorder = data.OutBillMaterial.GroupBy(x => new { x.TaskId, x.ProjectId }).Select(x => new { TaskId = x.Key.TaskId, ProjectId = x.Key.ProjectId }).ToList();

            //流转单按照任务单分组物料详情
            var ordermaterial = data.OutBillMaterial.GroupBy(x => new { x.TaskId, x.SubordinateProduct, x.Weight }).Select(x => new { TaskId = x.Key.TaskId, SubordinateProduct = x.Key.SubordinateProduct, Weight = x.Key.Weight, Nums = x.Sum(i => i.StatisticalQuantity) }).ToList();

            List<PB_OutsourcingBillUpList> OutsourcingBillUpList = new List<PB_OutsourcingBillUpList>();
            List<PB_OutsourcingBillDetail> OutsourcingBillDetail = new List<PB_OutsourcingBillDetail>();
            List<PB_OutsourcingBill> OutsourcingBill = new List<PB_OutsourcingBill>();

            int j = 1;

            foreach (var item in cirorder)
            {
                //查询任务单对应的流转卡明细信息
                var itemdata = data.OutBillMaterial.Where(x => x.TaskId == item.TaskId).ToList();
                //外包单Id
                string outbillid = IdHelper.GetId();
                foreach (var ciritem in itemdata)
                {
                    //外包单领取明细
                    PB_OutsourcingBillUpList BillUpList = new PB_OutsourcingBillUpList();
                    BillUpList.Id = IdHelper.GetId();
                    BillUpList.ParentId = outbillid;
                    BillUpList.LocalId = "";
                    BillUpList.MaterialId = ciritem.SubordinateProduct;
                    BillUpList.Nums = ciritem.StatisticalQuantity;
                    BillUpList.CreatorId = _operator.UserId;
                    BillUpList.CreatorRealName = userdata.RealName;
                    BillUpList.CreateTime = DateTime.Now;
                    BillUpList.Deleted = false;
                    BillUpList.CirBillId = ciritem.Id;
                    OutsourcingBillUpList.Add(BillUpList);
                }

                double? outbillweight = 0;

                //查询任务单物料明细信息
                var orderitem = ordermaterial.Where(x => x.TaskId == item.TaskId).ToList();
                foreach (var oitem in orderitem)
                {
                    //外包单明细
                    PB_OutsourcingBillDetail BillDetail = new PB_OutsourcingBillDetail();
                    BillDetail.Id = IdHelper.GetId();
                    BillDetail.CreatorId = _operator.UserId;
                    BillDetail.CreateTime = DateTime.Now;
                    BillDetail.Deleted = false;
                    BillDetail.MaterialId = oitem.SubordinateProduct;
                    BillDetail.Qty = oitem.Nums;
                    BillDetail.TheoreticalWT = oitem.Nums * oitem.Weight;
                    BillDetail.Weight = oitem.Weight;
                    BillDetail.ParentId = outbillid;
                    BillDetail.ReadyNums = oitem.Nums;
                    BillDetail.State = 1;
                    BillDetail.RejectNums = 0;
                    OutsourcingBillDetail.Add(BillDetail);
                    outbillweight += (oitem.Nums * oitem.Weight);
                }

                //外包单
                PB_OutsourcingBill Bill = new PB_OutsourcingBill();
                Bill.Id = outbillid;
                Bill.CreatorId = _operator.UserId;
                Bill.CreateTime = DateTime.Now;
                Bill.Deleted = false;
                Bill.BillCode = GetTimeStamp();
                Bill.OutsourcingDate = data.OutsourcingDate;
                Bill.RefCode = item.TaskId;
                Bill.OutsourcingCompanyId = data.OutsourcingCompanyId;
                Bill.ExpectedDate = data.ExpectedDate;
                Bill.ProjectId = item.ProjectId;
                Bill.WorkingProcedureId = data.SubId;
                Bill.TheoreticalWT = outbillweight;
                Bill.Price = data.Price;
                Bill.Amount = data.Price * outbillweight;
                Bill.Status = 2;
                Bill.DeptId = userdata.FactoryId;
                Bill.QrCode = o_outsour + "," + outbillid;
                Bill.OutName = data.OutsourcingCompanyName + "_" + DateTime.Now.ToString("yyyyMMdd") + "_" + (OutsourcingBilllist.Count + j);
                Bill.OutBillType = 1;
                OutsourcingBill.Add(Bill);

                j++;
            }

            await Db.InsertAsync<PB_OutsourcingBillUpList>(OutsourcingBillUpList);

            await Db.InsertAsync<PB_OutsourcingBillDetail>(OutsourcingBillDetail);

            await Db.InsertAsync<PB_OutsourcingBill>(OutsourcingBill);

        }

        #endregion

        #region 外包单装车

        /// <summary>
        /// 外包装车扫描外包单
        /// </summary>
        /// <param name="OutBillQrCode">外包单二维码</param>
        /// <param name="BillId">派车单Id</param>
        /// <returns></returns>
        public async Task<PB_OutsourcingBill> GetOutsourcingBillDataAsync(string OutBillQrCode, string BillId)
        {
            //外包单Id
            string outbillId = OutBillQrCode.Split(',')?[1].ToString();

            //查询派车单对应的外包单信息
            PB_TransportBillGroup TransportBillGroup = await Db.GetIQueryable<PB_TransportBillGroup>().Where(x => x.ParentId == BillId && x.DeliveryNoticeId == outbillId).FirstOrDefaultAsync();
            if (TransportBillGroup == null)
            {
                throw new BusException("请检查外包单与派车单是否匹配");
            }
            PB_OutsourcingBill OutsourcingBill = await Db.GetIQueryable<PB_OutsourcingBill>().Where(x => x.Id == outbillId && x.Status == 2).FirstOrDefaultAsync();
            if (OutsourcingBill == null)
            {
                throw new BusException("请检查外包单是否审核或备货");
            }
            return OutsourcingBill;
        }

        /// <summary>
        /// 获取派车单对应的外包单需要装车的物料
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<TranBillOutMaterialDTO>> GetTranBillOutMaterialListAsync(PageInput<TranBillOutMaterialConditionDTO> input)
        {
            var search = input.Search;
            string outbillId = search.OutBillQrCode.Split(',')?[1].ToString();
            string sql = @"select *,(ReadyNums-PackNums) as SurplusNums from (
select a.ParentId as BillId,a.DeliveryNoticeId as OutBillId,b.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,SUM(ReadyNums) as ReadyNums,IFNULL((select SUM(PackNums) from PB_TransportBillDetail where ParentId='{0}' and PreparationNoteId='{1}' and MaterialId=b.MaterialId),0) as PackNums from PB_TransportBillGroup as a
left join PB_OutsourcingBillDetail as b on a.DeliveryNoticeId=b.ParentId
left join PB_Material as c on b.MaterialId=c.Id
where a.ParentId='{0}' and a.DeliveryNoticeId='{1}'
GROUP BY a.ParentId,a.DeliveryNoticeId,b.MaterialId,c.`Code`,c.`Name`,c.Spec
) as t where 1=1 ";
            if (!search.Keyword.IsNullOrEmpty())
            {
                sql += @" and (MaterialName like '%"+ search.Keyword + "%' or MaterialCode like '%" + search.Keyword + "%') ";
            }
            string sqlMain = string.Format(sql, search.BillId, outbillId);
            var lidata = await Db.GetListBySqlAsync<TranBillOutMaterialDTO>(sqlMain);
            int num = lidata.Count;
            var list = lidata
               .Skip((input.PageIndex - 1) * input.PageRows)
               .Take(input.PageRows).ToList();
            return new PageResult<TranBillOutMaterialDTO> { Data = lidata, Total = num };
        }

        /// <summary>
        /// 外包单装车保存
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task SaveTranBillOutMaterialAsync(TranBillOutMaterialDTO data)
        {
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            PB_TransportBillDetail BillDetail = new PB_TransportBillDetail();
            BillDetail.Id = IdHelper.GetId();
            BillDetail.ParentId = data.BillId;
            BillDetail.DesignDetailId = "";
            BillDetail.PreparationNoteId = data.OutBillId;
            BillDetail.PackId = "";
            BillDetail.MaterialId = data.MaterialId;
            BillDetail.PretendCarUserId = _operator.UserId;
            BillDetail.PretendCarUserName = userdata.RealName;
            BillDetail.PretendCarTime = DateTime.Now;
            BillDetail.PackNums = data.Nums;
            BillDetail.ReceiptQuantity = 0;
            BillDetail.CreatorId = _operator.UserId;
            BillDetail.CreatorName = userdata.RealName;
            BillDetail.CreateTime = DateTime.Now;
            BillDetail.Deleted = false;
            BillDetail.Status = 0;
            BillDetail.JobId = "";

            await Db.InsertAsync<PB_TransportBillDetail>(BillDetail);
        }

        /// <summary>
        /// 获取外包单装车明细
        /// </summary>
        /// <param name="BillId">派车单Id</param>
        /// <returns></returns>
        public async Task<List<OutBillTransportBillDetailDTO>> GetOutBillTransportBillDetailAsync(string BillId)
        {
            Expression<Func<PB_TransportBillDetail, PB_Material, PB_OutsourcingBill, OutBillTransportBillDetailDTO>> packselect = (a, b, c) => new OutBillTransportBillDetailDTO
            {
                MaterialName = b.Name,
                MaterialCode = b.Code,
                MaterialSpec = b.Spec,
                OutName = c.OutName
            };
            packselect = packselect.BuildExtendSelectExpre();
            var packdata = from a in Db.GetIQueryable<PB_TransportBillDetail>().AsExpandable()
                           join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                           from b in ab.DefaultIfEmpty()
                           join c in Db.GetIQueryable<PB_OutsourcingBill>() on a.PreparationNoteId equals c.Id into ac
                           from c in ac.DefaultIfEmpty()
                           select @packselect.Invoke(a, b, c);
            return await packdata.Where(x => x.ParentId == BillId).ToListAsync();
        }

        /// <summary>
        /// 提交装车信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task SubmitOutBillTransportAsync(PB_TransportBillImgDTO data)
        {
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            //获取装车明细信息
            List<PB_TransportBillDetail> TransportBillDetail = await Db.GetIQueryable<PB_TransportBillDetail>().Where(x => x.ParentId == data.billId).ToListAsync();
            //获取装车的外包单
            var outbilldata = TransportBillDetail.GroupBy(x => new { x.PreparationNoteId }).Select(x => new { OutBillId = x.Key.PreparationNoteId }).ToList();
            List<string> OutBillIds = new List<string>();
            foreach (var item in outbilldata)
            {
                OutBillIds.Add(item.OutBillId);
            }
            string sqlMain = string.Empty;
            //修改外包单状态
            sqlMain += @" update PB_OutsourcingBill set Status=3 where Id in ('" + string.Join("','", OutBillIds.ToArray()) + "'); ";
            //修改派车单状态
            sqlMain += @" update PB_TransportBill set State=2 where Id='" + data.billId + "'; ";

            List<PB_TransportBillImg> list = new List<PB_TransportBillImg>();
            foreach (var item in data.listdata)
            {
                PB_TransportBillImg pbdata = new PB_TransportBillImg();
                pbdata.Id = IdHelper.GetId();
                pbdata.BillId = data.billId;
                pbdata.Img = item;
                pbdata.CreatorId = _operator.UserId;
                pbdata.CreatorName = userdata.RealName;
                pbdata.CreateTime = DateTime.Now;
                pbdata.Deleted = false;
                list.Add(pbdata);
            }
            await Db.InsertAsync<PB_TransportBillImg>(list);

            await ExecuteSqlAsync(sqlMain);
        }

        /// <summary>
        /// 移除装车明细
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task DeleteOutBillTransportBillDetailAsync(OutBillTransportBillDetailDTO data)
        {
            string sqlMain = @" delete from PB_TransportBillDetail where Id='" + data.Id + "'";
            await ExecuteSqlAsync(sqlMain);
        }

        #endregion

        #region 外包单回货

        /// <summary>
        /// 获取外包单位下未完全回货的外包单
        /// </summary>
        /// <param name="OutsourcingCompanyId">外包单位Id</param>
        /// <returns></returns>
        public async Task<List<PB_OutsourcingBill>> GetOutsourcingBillReturnAsync(string OutsourcingCompanyId)
        {
            List<PB_OutsourcingBill> OutsourcingBill = await Db.GetIQueryable<PB_OutsourcingBill>().Where(x => x.OutsourcingCompanyId == OutsourcingCompanyId && x.Status >= 4).ToListAsync();
            return OutsourcingBill;
        }

        /// <summary>
        /// 获取外包回派车单信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<PB_TransportBill>> GetTransportBillOutReturnAsync()
        {
            return await Db.GetIQueryable<PB_TransportBill>().Where(x => x.BillType == "Outsourcing_I" && x.State < 4 && x.State >= 1).OrderByDescending(x => x.CreateTime).ToListAsync();
        }

        /// <summary>
        /// 获取外包单的明细信息
        /// </summary>
        /// <param name="OutBillId">外包单Id</param>
        /// <returns></returns>
        public async Task<List<OutMaterialReturnDTO>> GetOutMaterialReturnListAsync(string OutBillId)
        {
            string sql = @"select *,(QtyNums-ReturnNums) as SurplusNums from (
select a.ParentId as OutBillId,a.MaterialId,b.`Code` as MaterialCode,b.`Name` as MaterialName,b.Spec as MaterialSpec,SUM(Qty) as QtyNums,IFNULL((select SUM(Qty) from PB_OutsourcingBillReturnDetail where ParentId='{0}' and MaterialId=a.MaterialId),0) as ReturnNums from PB_OutsourcingBillDetail as a
left join PB_Material as b on a.MaterialId=b.Id
where a.ParentId in (select Id from PB_OutsourcingBill where Id='{0}' and Status>=4 and Status!=6) 
group by a.ParentId,a.MaterialId,b.`Code`,b.`Name`,b.Spec
) as t  where QtyNums!=ReturnNums ";
            string sqlMain = string.Format(sql, OutBillId);
            return await Db.GetListBySqlAsync<OutMaterialReturnDTO>(sqlMain);
        }

        /// <summary>
        /// 外包单保存
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [Transactional]
        public async Task SaveOutMaterialReturnAsync(OutMaterialReturnDataDTO data)
        {
            //收货单Id
            string recId = IdHelper.GetId();

            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            //获取本次保存有几个外包单
            var outbilldata = data.OutMaterialReturn.GroupBy(x => new { x.OutBillId }).Select(x => new { OutBillId = x.Key.OutBillId }).ToList();
            List<string> outbillids = new List<string>();
            foreach (var item in outbilldata)
            {
                outbillids.Add(item.OutBillId);
            }

            //获取外包单明细信息
            List<PB_OutsourcingBillDetail> OutsourcingBillDetail = await Db.GetIQueryable<PB_OutsourcingBillDetail>().Where(x => outbillids.Contains(x.ParentId)).ToListAsync();

            //获取外包单的已回货明细信息
            List<PB_OutsourcingBillReturnDetail> ReturnDetailList = await Db.GetIQueryable<PB_OutsourcingBillReturnDetail>().Where(x => outbillids.Contains(x.ParentId)).ToListAsync();

            //添加外包单回货明细
            List<PB_OutsourcingBillReturnDetail> OutsourcingBillReturnDetail = new List<PB_OutsourcingBillReturnDetail>();
            
            foreach (var item in data.OutMaterialReturn)
            {
                if (item.Nums > 0)
                {
                    PB_OutsourcingBillReturnDetail ReturnDetail = new PB_OutsourcingBillReturnDetail();
                    ReturnDetail.Id = IdHelper.GetId();
                    ReturnDetail.CreatorId = _operator.UserId;
                    ReturnDetail.CreateTime = DateTime.Now;
                    ReturnDetail.Deleted = false;
                    ReturnDetail.MaterialId = item.MaterialId;
                    ReturnDetail.Qty = item.Nums;
                    ReturnDetail.TheoreticalWT = (item.Nums * item.Weight) == null ? 0 : (item.Nums * item.Weight);
                    ReturnDetail.Weight = item.Weight == null ? 0 : item.Weight;
                    ReturnDetail.ParentId = item.OutBillId;
                    ReturnDetail.BillId = data.BillId;
                    ReturnDetail.RecId = recId;
                    OutsourcingBillReturnDetail.Add(ReturnDetail);
                    ReturnDetailList.Add(ReturnDetail);

                    
                }
            }

            //生成收货单
            var recdata = data.OutMaterialReturn.GroupBy(x => new { x.MaterialId, x.Price }).Select(x => new { MaterialId = x.Key.MaterialId, Price = x.Key.Price, Nums = x.Sum(i => i.Nums) }).ToList();
            List<TD_RecDetail> RecDetail = new List<TD_RecDetail>();
            //卸车明细
            List<PB_TransportBillDetail> BillDetail = new List<PB_TransportBillDetail>();
            double? recnums = 0;
            double? rectotalnum = 0;
            foreach (var item in recdata)
            {
                TD_RecDetail recd = new TD_RecDetail();
                recd.Id = IdHelper.GetId();
                recd.RecId = recId;
                recd.StorId = data.StorId;
                recd.MaterialId = item.MaterialId;
                recd.Price = Convert.ToDouble(item.Price);
                recd.PlanNum = Convert.ToDouble(item.Nums);
                recd.RecNum = Convert.ToDouble(item.Nums);
                recd.InNum = 0;
                recd.Amount = Convert.ToDouble(item.Price * item.Nums);
                recd.CreateTime = DateTime.Now;
                recd.CreatorId = _operator.UserId;
                recd.Deleted = false;
                recd.TaskId = "";
                recd.CirBillId = "";
                RecDetail.Add(recd);
                recnums += item.Nums;
                rectotalnum += Convert.ToDouble(item.Price * item.Nums);

                PB_TransportBillDetail billd = new PB_TransportBillDetail();
                billd.Id = IdHelper.GetId();
                billd.ParentId = data.BillId;
                billd.DesignDetailId = "";
                billd.PreparationNoteId = "";
                billd.PackId = "";
                billd.MaterialId = item.MaterialId;
                billd.PretendCarUserId = _operator.UserId;
                billd.PretendCarUserName = userdata.RealName;
                billd.PretendCarTime = DateTime.Now;
                billd.PackNums = item.Nums;
                billd.ReceiptQuantity = item.Nums;
                billd.CreatorId = _operator.UserId;
                billd.CreatorName = userdata.RealName;
                billd.CreateTime = DateTime.Now;
                billd.Deleted = false;
                billd.Status = 1;
                billd.ReceivingRemarks = "";
                billd.JobId = "";
                BillDetail.Add(billd);
            }

            if (recnums == 0)
            {
                throw new BusException("请填写本次回货数量");
            }

            TD_Receiving Receiving = new TD_Receiving();
            Receiving.Id = recId;
            Receiving.StorId = data.StorId;
            Receiving.Code = GetTimeStamp();
            Receiving.OrderTime = DateTime.Now;
            Receiving.RecTime = DateTime.Now;
            Receiving.Type = "HalfProduct";
            Receiving.RefCode = "";
            Receiving.Status = 0;
            Receiving.TotalNum = Convert.ToDouble(recnums);
            Receiving.InNum = 0;
            Receiving.TotalAmt = Convert.ToDouble(rectotalnum);
            Receiving.CreateTime = DateTime.Now;
            Receiving.CreatorId = _operator.UserId;
            Receiving.Deleted = false;
            Receiving.QrCode = h_TdRe + "," + recId;
            Receiving.DeptId = userdata.FactoryId;
            Receiving.UpdateTime = DateTime.Now;


            string sqlMain = string.Empty;
            //循环比较外包单
            foreach (var item in outbilldata)
            {
                //外包单已发数据信息
                var itemBillDetail = OutsourcingBillDetail.Where(x => x.ParentId == item.OutBillId).ToList();

                //默认满足
                bool? itembool = true;

                //比较已发和已回
                foreach (var itemDetail in itemBillDetail)
                {
                    //外包单已回货数据信息
                    var itemBillReturnDetail = ReturnDetailList.Where(x => x.ParentId == item.OutBillId && x.MaterialId == itemDetail.MaterialId).ToList();
                    var itemMaterialdata = itemBillReturnDetail.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Qty) }).FirstOrDefault();
                    if (itemMaterialdata != null)
                    {
                        if (itemMaterialdata.Nums == itemDetail.Qty)
                        {
                            if (itembool != false)
                            {
                                itembool = true;
                            }
                        }
                        else
                        {
                            itembool = false;
                        }
                    }
                }

                if (itembool == true)
                {
                    sqlMain += @" update PB_OutsourcingBill SET Status=6 where Id='" + item.OutBillId + "'; ";
                }
                else
                {
                    sqlMain += @" update PB_OutsourcingBill SET Status=5 where Id='" + item.OutBillId + "'; ";
                }
            }




            sqlMain += @" update PB_TransportBill set State=3 where Id='" + data.BillId +"'; ";

            //获取所有的外包单
            List<PB_OutsourcingBill> OutsourcingBill = await Db.GetIQueryable<PB_OutsourcingBill>().Where(x => outbillids.Contains(x.Id)).ToListAsync();
            foreach (var item in OutsourcingBillReturnDetail)
            {
                var itemdata = OutsourcingBill.Where(x => x.Id == item.ParentId).FirstOrDefault();
                if (!itemdata.RefCode.IsNullOrEmpty())
                {
                    sqlMain += @" update PB_ProductionOrderDetail SET CompleteNums=IFNULL(CompleteNums,0)+" + item.Qty + " where MaterialId='" + item.MaterialId + "' and ParentId='" + itemdata.RefCode + "'; ";
                }
            }


            await Db.InsertAsync<PB_OutsourcingBillReturnDetail>(OutsourcingBillReturnDetail);

            await Db.InsertAsync<PB_TransportBillDetail>(BillDetail);

            await Db.InsertAsync<TD_RecDetail>(RecDetail);

            await Db.InsertAsync<TD_Receiving>(Receiving);

            await Db.ExecuteSqlAsync(sqlMain);

        }


        /// <summary>
        /// 外包回货列表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<PB_OutsourcingBillViewDTO>> GetDataReturnListAsync(PageInput<PB_OutsourcingBilSelectDTO> input)
        {
            Expression<Func<PB_OutsourcingBill, PB_ProductionOrder, PB_Project, PB_Supplier, PB_OutsourcingBillViewDTO>> select = (a, b, c, d) => new PB_OutsourcingBillViewDTO
            {
                ProdName = b.POName,
                ProjectName = c.Name,
                SupplierName = d.Name
            };
            var search = input.Search;
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_ProductionOrder>() on a.RefCode equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Project>() on a.ProjectId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Supplier>() on a.OutsourcingCompanyId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d);
            if (!search.Status.IsNullOrEmpty())
            {
                q = q.Where(x => x.Status == search.Status);
            }
            if (!search.InputName.IsNullOrEmpty())
            {
                q = q.Where(x => x.Id.Contains(search.InputName) || x.ProjectName.Contains(search.InputName));
            }
            q = q.Where(x => x.Status > 4);
            return await q.GetPageResultAsync(input);

        }

        /// <summary>
        /// 获取外包单的回货明细
        /// </summary>
        /// <param name="OutBillId">外包单Id</param>
        /// <returns></returns>
        public async Task<List<OutsourcingBillReturnDetailDTO>> GetOutsourcingBillReturnDetailAsync(string OutBillId)
        {
            Expression<Func<PB_OutsourcingBillReturnDetail, PB_Material, PB_TransportBill, OutsourcingBillReturnDetailDTO>> select = (a, b, c) => new OutsourcingBillReturnDetailDTO
            {
                PlateNO = c.PlateNO,
                MaterialCode = b.Code,
                MaterialName = b.Name,
                MaterialSpec = b.Spec
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_OutsourcingBillReturnDetail>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_TransportBill>() on a.BillId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    select @select.Invoke(a, b, c);
            return await q.Where(x => x.ParentId == OutBillId).ToListAsync();

        }


        #endregion

        #endregion

        #region 私有成员

        #endregion
    }
}